1. Create a new Empty Database called "Final"
2. CREATE TABLES
The create table statements should work without errors, have the specified data types,
foreign keys, primary keys and all other constraints.
3. INSERT DATA
The inserts should run without error and the proper number of records containing the
proper data types should be in each table. Constraints must be respected throughout.
For 4 - 7
Use the Northwind database for answering all queries, sub-queries, views,
and FIX the Code sections. -- You created the Northwind database in the Unit 9
assignment by running the instnwnd.sql script located in doc sharing.
4. CREATE VIEWs
The views should display the correct data and run without error.
5. CREATE QUERIES
Queries should display the correct result set and run without error.
6. CREATE SUB-QUERIES
Sub-queries should display the correct result set and run without error.
7. FIX the CODE
Make sure to read the instructions carefully under the "FIX the CODE"
1. Create a new Empty Database
called "Final_db"
1. CREATE 3 tables in Final_db
With the following appropriate and correct (syntax, readability, primary keys, data types and
lengths, and foreign key creation).
Create the following 3 tables.
Choose appropriate data types and create all specified...
NOTE**** You must have all the following constraints properly defined in
order to receive any points on the final project
Primary and Foreign key constraints and "Named" CHECK Constraints.
Table: DEPARTMENTS
----------------------------------------- --------
DEPARTMENT_ID Primary Key
DEPARTMENT_NAME NOT NULL
MANAGER_ID FK refers to PK of Employees table.
LOCATION_ID FK refers to PK of Locations table.
Table: EMPLOYEES
----------------------------------------- --------
EMPLOYEE_ID Primary Key
FIRST_NAME NOT NULL
LAST_NAME NOT NULL
EMAIL
SALARY NOT NULL MUST BE > 10,000 and < 500,000
INCOMETXRATE NOT NULL MUST BE 5, 10, or 15
PHONE_NUMBER
HIRE_DATE NOT NULL
MANAGER_ID FK refers to the PK of the Employees table.
DEPARTMENT_ID FK refers to the PK of the Departments table.
Table: LOCATIONS
----------------------------------------- --------
LOCATION_ID Primary Key
DESCRIPTION NOT NULL
POSTAL_CODE NOT NULL
CITY NOT NULL
STATE_PROVINCE NOT NULL
COUNTRY NOT NULL Default 'United States'
3. INSERT data for each table.
(Make up appropriate data for each table - respecting Data Types and Foreign Keys)
Your insert SQL Statements need to have the required number of rows in each table, run
without error and be in the proper order so that Foreign key constraints do not fail.
Insert 4 departments, 10 employees, and 3 locations.
Make sure to use all locations within the departments table
Make sure to use all departments within the employees table
For 4 - 7
Use the Northwind database for answering all queries, sub-queries, views,
and FIX the Code sections. -- You created the Northwind database in the Unit 9
assignment by running the instnwnd.sql script located in doc sharing.
4. Create Views
#1: CREATE VIEW
Create a VIEW that returns the total number of daily orders placed in July 1996
Display the Number of Orders and the order date.
#2 CREATE VIEW
Use a Sub-query and Logical operators: AND, OR
Create a View that return the orders placed in July 1996 by Nancy Davolio,
Janet Leverling, and Steven Buchanan. List the order id,
employee id, and the order date.
#3 CREATE VIEW
Create a View that returns all employees with last name containing the letter 'a' twice or more.
Display the employee id, first and last names
5. Create Three Queries
#1: Query
prepare a query that returns the three ship countries with the highest average freight in 1996.
List the country and the average freight.
#2: Query
prepare a query that returns the employees' id who handled orders 10248, 10249, and 10250.
List the orderid, employeeid, and the order date
#3: Query
Using the Logical operators: AND, OR, prepare a query that returns the orders placed in July 1996
by Nancy Davolio, Janet Leverling, and Steven Buchanan. List the order id, employee id, and
the order date. Order the result set by the employeeID
6. Create Sub-Queries
#1: SUB-QUERY
prepare a sub-query that returns all orders placed on the last day of activity that can be found in
the Orders table. Include the orderid, orderdate, customerid, and the employeeid.
#2: SUB-QUERY
prepare a sub-query that returns the total number of orders placed by the customer with the
highest number of orders. Include the customerid and the number of orders placed.
#3: SUB-QUERY
prepare a sub-query that returns employees who did not place orders on or after May 1st, 1998.
Include the employeeid, last and first names.
7. Fix-It (3 problems)
NOTES: In all cases more than one fix in the T-SQL script will be necessary for the result set to
look similar to the display shown below each query. You must fix the code that is provided here.
Using another code in order to display the correct result will not be accepted
Troubleshooting the code is one of the best learning techniques for enhancing the critical
thinking and problem solving skills.
FIX #1:
--Use a subquery that returns the monetary values of all orders that have discounts
--greater than 15%
--List the orderid and the order value this last with the highest value at the top
SELECT SUM(od.orderid) As OrderID,
(formula?) AS [Order Values]
FROM [Order Details] od
WHERE od.Discount =
(SELECT od.Discount
FROM [Order Details] od
GROUP BY od.discount
HAVING od.discount >.15)
GROUP BY od.quantity, od.discount, od.UnitPrice
ORDER BY [Order Values] ASC;
FIX #2:
-- prepare a sub-query that returns all orders placed on the last day of
-- activity that can be found in the Orders table
SELECT orderid, orderdate, customerid, employeeid
FROM Orders
WHERE orderdate = MAX(orderdate)
FIX #3:
prepare a query that returns the name of the city
with the highest number of orders placed.
SELECT MAX(o.OrderID) AS [Number of Orders], od.ShipCity
FROM Orders o, [Order Details] od
GROUP BY o.ShipCity
ORDER BY [Number of Orders] DESC