glass
pen
clip
papers
heaphones

MySQL Questions

MySQL Questions

Description

Note to all students 

The lab A is scheduled to take 60 to 90  minutes. All work to be completed during the class time. The submission is a text file with your answers. 

You are allow to use your books and notes for this lab. 

A data set is available for you named Lab3Fall-sql.txt

You need to input this file in Oracle live SQL (all students should have registered to obtain a free student account). 

The total grading of the Lab is 2.5 points.

PART A  – Working with a Data Set in Oracle SQL Live (Guided)

Run the data set in Oracle live SQL.

A error might occur on the drop statements at the start of the execution but ignore that.

Reason: The first lines of the file (DROP xxx) are put there to in case you run the program more than once make sure that all tables are visible and contain data. 

Once you have the data set up and running you will find a number of tables.

PART B – Answer the following questions (in a text/word file to be uploaded in Canvas)

B1. Briefly describe each of those tables giving its name, attributes and purpose 

B2.  Using the data available answer the following question: Which customers bought a natural ash computer desk? 

In order to get this info you will need to:

2.1 Proceed to join the tables before extracting any useful information . Join the tables by giving them the following names : 

CUSTOMER_T CUS
ORDER_T ORD
ORDERLINE_T ORL

Look for the matching criteria and join them using those criteria.

Provide a copy of the SQL query you have used for this purpose as the answer for this section.

2.2. Now we have effectively a single table of all the data. State the SQL query related to select customers that bought natural ash computer desks.

( Hint: PRODUCTID of natural ash computer desk is 3)  

2.3 Finally, provide the  results of the query in 2.2 as your answer for this section plus a pic of the table.

B3. Using joints and conditionals 

3.1 Do a query in the same tables of section 2 to find all products made in cherry and all products made in walnut.