Posted: September 13th, 2017

DML (Data Manipulation Language) and Queries

DML (Data Manipulation Language) and Queries

use Oracle SQL database to complete this work. This work is on Relational Database systems and  use Oracle SQL database to complete the work.

The Specific instruction for part 4 of this project is attach. Please use these instruction to complete part 4

PART 4 – DML (Data Manipulation Language) and Queries
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);
.
Learning Demonstration for part 4
Now you have completed the definition part of your database schema. The next step is to enter data into your tables and then query your data.
a.    Once all objects have been created in the database, create SQL INSERT statements (DML) to populate each table with sample data. Each table should have a minimum of 10 rows unless you have specific business rules that prevent it from having that many records. Make sure your sequences and triggers are valid and enabled so that surrogate keys and audit columns can be populated automatically. For example:
INSERT INTO DEPARTMENT(DEPT_NAME, DEPT_LOCATION)
VALUES(‘HR’, ‘Adelphi, MD’);
INSERT INTO DEPARTMENT(DEPT_NAME, DEPT_LOCATION)
VALUES(‘Sales’, ‘College Park, MD’);
COMMIT;
b.    After entering sample data into each table, develop SQL SELECT statements to query your tables. You should have a minimum of 20 SQL select statements. Query 1 to 12 are basic queries, plus at least 8 advanced queries. Each query should have comment/description to explain its business purpose.
i.    Select all columns and all rows from one table. For example:
— this query selects all information about all departments
SELECT * FROM DEPARTMENT;
ii.    Select 5 columns and all rows from one table.
iii.    Select all columns and all rows from one view.
iv.    Using a join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product.
v.    Select and order data retrieved from one table.
vi.    Using a join on 3 tables, select 5 columns and 10 rows from the 3 tables.
vii.    Select distinct rows using joins on 3 tables.
viii.    Use group by & having in a select statement using one or more tables.
ix.    Use IN clause to select data from one or more tables.
x.    Select Length of one column from one table (use Length function)
xi.    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. For example:
— delete the HR department
DELETE FROM DEPARTMENT WHERE DEPT_NAME = ‘HR’;
— revert the change
ROLLBACK;
xii.    Use the SQL UPDATE statement to change some data. You can either COMMIT or ROLLBACK afterwards. For example:
— change the location of HR department
UPDATE DEPARTMENT SET DEPT_LOCATION = ‘Largo, MD’ WHERE DEPT_NAME = ‘HR’;
— revert the change
ROLLBACK;
xiii.    Perform 8 additional advanced (multiple table joins, sub-queries, aggregate, etc.) SQL statements.