Your task for this assignment is to use SQL to implement the basic relational operations of projection, selection and joining. 1. Implement basic selection, projection and join relational operations using SQL using one of: a. a Microsoft Access database named csc231database.accdb b. a MYSQL database imported from text file csc231database.sql. This database contains two tables named CUSTOMERS and ORDERS. To implement selection, use SQL to create a new relation containing all order attributes, but only those orders without a status of “shipped”. To implement projection, use SQL to create a new relation containing the three attributes customer number, customer name and phone for all customers. To implement join, use SQL to create a new relation containing the four attributes order number, order status, customer number and country. This relation should represent all orders for customer that are not located in USA.
Your task for this assignment is to use SQL to implement the basic relational operations of
projection, selection and joining.
1. Implement basic selection, projection and join relational operations using SQL using one of:
a. a Microsoft Access
b. a MYSQL database imported from text file csc231database.sql.
This database contains two tables named CUSTOMERS and ORDERS. To implement
selection, use SQL to create a new relation containing all order attributes, but only those
orders without a status of “shipped”. To implement projection, use SQL to create a new
relation containing the three attributes customer number, customer name and phone for
all customers. To implement join, use SQL to create a new relation containing the four
attributes order number, order status, customer number and country. This relation should
represent all orders for customer that are not located in USA.
2. If using the MS Access database, verify availability of the CUSTOMERS and ORDERS tables
using the steps described here. To examine the contents of the CUSTOMERS table, do the
following:
• type ALT-C-Q-D to enter query design mode;
• disregard and close the "show table" dialogue box;
• type ALT-J-Q-W-Q to enter SQL view;
• type the SQL query "select * from customers;"
• type ALT-J-Q-G to execute the SQL query above and display all tuples in the
CUSTOMERS table;
To examine the contents of the ORDERS table, repeat the steps above using the SQL query
"select * from orders;".
To save an SQL query, copy and paste the SQL query to another document.
When the results of running an SQL query are displayed, save the results of the SQL query
as follows:
• type ALT-X-T to export SQL query results to a text file;
• choose a meaningful file name and check the checkbox for "Export data with
formatting and layout";
• to navigate among query tabs, type CTRL-F6
• to close a query tab, type CTRL-W
3. If using the MYSQL database, verify availability of the CUSTOMERS and ORDERS tables using
the steps described here. To examine the contents of the CUSTOMERS and ORDERS tables,
do the following:
• mysql -u root -e "create database css231database"
• mysql -u root -D csc231database < csc231database.sql
• mysql -u root -e "select * from customers"
• mysql -u root -e "select * from orders"
To save an SQL query, copy and paste the SQL query to another document.
Save the results of an SQL query as follows:
• mysql -u root -e "select * from orders" > myorders.txt
4. After each select, project and join SQL query, save query and the results (as described
above) in a documented text file. The text file is named csc231_prog3_lastname.txt.
Include the following information at the top of the text file:
a. the ID, section and name of the course;
b. your name;
c. this file name;
d. the program assignment number and due date;
e. the program purpose;
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
