Database Management Systems
Class Assignment s
10 points
Task: Database Creation (Store – imaginary store sells items such as books, videos, DVDs, CDs) and execute queries on it Live SQL
Instructions to follow:
- Download the store_schema (SQL text file) from Canvas
- Login to your LiveSQL account in Oracle Live SQL:
Go to MyScripts on the left ribbon
Then click on ‘Upload Script’ on top right, and upload store_schema.sql file.
Then click on ‘Run Script’ on top right of the screen.
This will create the Store_Schema tables. Then go to SQL Worksheet on the left and start coding.
NOTE: You will need to run Store_schema.sql script everytime you sign out of your account. To do this after you log in you need to go to myscripts on the left, select your script, and run script again to add tables to your database.
- Once database has been created type and execute the following SQL statements.
- SELECT table_name
FROM user_tables;
How many tables have been created in the ‘Store’ database ? Paste the results screen shot of the Oracle screen.
- SELECT *
FROM customers;
How many customers are there in this table?
Paste the screen shot of the results.
- Write and execute the SQL to find out ‘all’ the ‘products’ being sold by this store.
Paste the ‘Oracle screen shot’ of the SQL and results.
- Write and execute the SQL to find out who is the sales manager of the store. Paste the ‘Oracle screen shot’ of the SQL and results.
- Write and execute the SQL to retrieve rows with combined first and last names of all the customers. Results should have a ‘ _’ separating first with last name in the combined name and should display in ascending order
Paste the ‘Oracle screen shot’ of the SQL and results.
- Write and execute the SQL to retrieve the ‘description’ of the products in the Products table – under a changed heading in the query results. Hint: Use alias method and the results should be sorted in descending order.
Paste the ‘Oracle screen shot’ of the SQL and results.
- Write and execute SQL on any of the tables in Store database using the following commands: For each of them explain the ‘business need’.
- IN operator
- Any of the arithmetic operators (+ – / *)
- ‘AND’ and ‘OR’ operators together in one SQL
- LIKE operator
Paste the ‘Oracle screen shot’ of the SQL and results.
NOTE: Handy commands to use throughout the assignment:
- If you need to check the data in any table – SELECT * FROM (type table name);
- Command to clear your screen in Oracle SQL Plus: CLEAR SCR;
- Command to look at any table columns – DESC (table name);