Write ten sql statements against the student schema

Assignment Help PL-SQL Programming

Reference no: EM131309304

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).

Reference no: EM131309304

Questions Cloud

Revise the given passage to avoid platitudes : Revise the given passage to avoid platitudes, obvious flattery, and exaggeration.- You, our loyal and dedicated employees, have always been the most qualified and the hardest working in the industry.

Relationship with a potential business partner : 1. Write an overview of how you would approach building a relationship with a potential business partner in a global market. What means of communication and contact might you use and for what purposes or circumstances might you vary the type of co..

Show the quantity of imported steel : Cranberry production in Massachusetts totaled 2.37 million barrels in 2008, a 56 percent increase from the 1.52 million barrels produced in 2007. Demand increased by even more than supply, pushing 2008 prices to $56.70 per barrel from $49.80 in 20..

Describe social psychological phenomena : Identify contextual variables (e.g., culture) that impact psychological adjustment.Define stress, stressors, and coping strategies, and contemplate their relationship to health and wellness.Identify and describe social psychological phenomena.Describ..

Write ten sql statements against the student schema : Write 10 SQL statements against the STUDENT schema you created for practice lab 1. Your statements should run error-free and should be valid.

Write a memo recommending elizabeth larson : Write a memo to Robert Underwood, the vice president, recommending one of these candidates.- Assume that personality is the most important criterion, and write a memo recommending Elizabeth Larson.

How much weight should you give to the workers : Also, how much weight should you give to the workers' clear preference for not following the regulations: ethically, can safety standards be relaxed if those to whom they apply want them to be relaxed

Prepare presentation on a brief history of personal computer : Prepare a presentation on A Brief History of the Personal Computer. A PowerPoint Presentation (2013) made of 8-10 slides based on the topic. Please make sure you save it on a flash drive/usb. You will have 3 minutes to present it.

Describe a command economy and a laissez-faire economy : Plot this data on a production possibilities graph and explain why the data shows that Rougarou experiences increasing opportunity costs.

User Account

All Pages