Description

Please see attached file Project 1 & 2. I need both parts done. All information is based off of the erd attached file Part 1 Create the objects (attributes, entities & relationships etc) as defined in the ERD using SQL DDL statements which will include the following: Drop statements for all objects in the lab project (drop existing objects first so that you can rerun your script without error). Create statements for all tables and keys of your project. Create indexes on natural key columns, foreign keys, and other columns that will be frequently used as query filters. Create views. Create sequences.

 Create triggers. Check content of the catalog/data dictionary. This section should contain SQL SELECT statements to query DBMS catalog/data dictionary (such as user_objects or user_tables) to demonstrate the proper functioning of each object. 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. See Learning Demonstration posted under Course Content/Hands-on Resources for step-by-steop instructions. Part 2 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. 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 and 10 rows from the 3 tables. 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. 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. You can either COMMIT or ROLLBACK afterwards. 13. 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 (doc/docx/pdf) including all four parts of projects (SOW, requirements, ERD, your code as well as results of running your code). The instructor and TA should be able to run your source file as script and generate the same output as shown in your second file. See Learning Demonstration posted under Course Content/Hands-on Resources for step-by-step instructions