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 -D csc231database -e "select * from customers"
• mysql -u root -D csc231database -e "select * from orders"
To save an SQL query, copy and paste the SQL query to another document.
To save the results of an SQL query, do the following:
• mysql -u root -e "select * from orders" > myorders.txt
4. After each select, project and join SQL query described in paragraph 1 above, save query and the results in one 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;
5. Submit your documented SQL query result text file as an attachment to an email message to kbyron@bmcc.cuny.edu using a subject in this form: “csc231_prog3_lastname”.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
