SQL Assignment

Write 10 SQL statements against the STUDENT schema you created for practice lab 1. Your statements should run error-free and should be valid. Submit two separate files: one plain text file (.txt or .sql file) with your statements only; and the other document (doc/docx/pdf) include both your statements and your query results (copy and paste text or screen shots).

Step 1

1. Display all information about all courses

2. Add a new course "Relational Database Systems" with course number 651. Use your user name as created_by/modified_by and current date as created_date/modified_date

3. List the course number for "Database System Principles"

4. Set "Database System Principles" as the prerequisite for "Relational Database Systems"

5. List all courses that have "Database System Principles" as prerequisite
6. List total number of courses that have no prerequisite

7. Delete course "Relational Database Systems"

8. Display total number of courses

9. Lower cost of all courses by 100

10. List all course cost (show course description and cost only) by the order of cost from highest to lowest

Step 2

1. Drop statements for all objects in the lab project (drop existing objects first so that you can rerun your script without error).

2. Create statements for all tables and keys of your project.

3. Create indexes on natural key columns, foreign keys, and other columns that will be frequently used as query filters (i.e., Columns in the "WHERE" clause).

4. Create at least two views.

5. Create at least two sequences.

6. Create at least two triggers.

7. Check content of the catalog/data dictionary.

Item 7 (above) should contain SQL SELECT statements to query DBMS catalog/data dictionary (such as user_objects or user_tables) to demonstrate all objects are created successfully.

The deliverable would be two separate files: one plain text file (.txt or .sql file) with your SQL statements only; and the other document (doc/docx/pdf) include both your SQL statements and the output (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document.

Step 3

Once all objects have been created in the database, create SQL INSERT statements (DML) to populate each table with sample data. Then develop SQL SELECT statements to query your tables.

• INSERT statements - each table should have a minimum of 10 rows unless you have specific business rules that prevent it from having that many records.

• SELECT Statements - minimum 20 Queries.

Note: You should have a minimum of 20 SQL select statements. Query 1 to 12 (see below) are required, plus at least 8 advanced queries. Each query should have comment/description to explain its business purpose, as well as which requirement item you are satisfying (i.e., --1. Select all columns and all rows from one table). Please submit both query statements and query results.

1. Select all columns and all rows from one table.

2. Select 5 columns and all rows from one table.

3. Select all columns and all rows from one view.

4. Using a join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product.

5. Select and order data retrieved from one table.

6. Using a join on 3 tables, select 5 columns from the 3 tables. Use syntax that would limit the output to 10 rows.

7. Select distinct rows using joins on 3 tables.

8. Use GROUP BY & HAVING in a select statement using one or more tables.

9. Use IN clause to select data from one or more tables.

10. Select length of one column from one table (use LENGTH function).

11. Use the SQL DELETE statement to delete one record from one table. Add select statements to demonstrate the table contents before and after the DELETE statement. Make sure to use ROLLBACK afterwards so that the data will not be physically removed.

12. Use the SQL UPDATE statement to change some data. Add select statements to demonstrate the table contents before and after the UPDATE statement. You can either COMMIT or ROLLBACK afterwards.

13. Using Coronel, Morris and Rob's definition of Advanced SQL (Chapter 8), perform 8 additional advanced (multiple table joins, sub-queries, aggregate, etc.) SQL statements.

The deliverable would be two separate files: a consolidated source file in plain text (.txt or .sql file) including all your SQL statements (DDL to create your objects, INSERT statements to populate your tables, and your queries); and a consolidated, formatted Word document including all four parts of projects (SOW, requirements, ERD, your code as well as results of running your code).