SQL ASSESSMENT , Sql Server

SQL ASSESSMENT

/*
SCHEMA

Below is the basic schema for a fictional business.
Rextester does not save schema so you will have to run the create/insert statements
along with any select statements while you test your answers
*/

CREATE TABLE dbo.Contacts
    (
        ContactId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,
        Active BIT NOT NULL
            CONSTRAINT DF_Contacts_Active
                DEFAULT ( 1 ) ,
        FirstName NVARCHAR(100) ,
        MiddleName NVARCHAR(100) ,
        LastName NVARCHAR(100) ,
        DOB DATE ,
        ModifiedDate DATETIME
            CONSTRAINT DF_Contacts_ModifiedDate
                DEFAULT ( GETDATE())
    );

INSERT dbo.Contacts ( Active ,
                      FirstName ,
                      MiddleName ,
                      LastName ,
                      DOB ,
                      ModifiedDate )
VALUES ( 0, N'William', N'James', N'Murray', '9/21/1950', '1/1/2018 3:01:47' ) ,
       ( 1, N'Judith', N'Olivia', N'Dench', '12/9/1934', '3/10/2011 17:52:00' ) ,
       ( 1, N'Terry', N'Alan', N'Crews', '7/30/1968', '9/28/2014 12:41:31' ) ,
       ( 1, N'Pamela', N'Suzette', N'Grier', '5/26/1949', '1/1/2018 11:52:23' ) ,
       ( 1, 'John', 'S.', 'Doe', '1/1/2000', '7/10/2018 19:43:22' ) ,
       ( 1, 'Jane', 'K.', 'Smith', '4/16/1987', '2/2/2015 9:00:00' ) ,
       ( 1, 'Henry', 'D.', 'Williams', '7/21/92', '5/18/17 7:29:10' ) ,
       ( 1, 'Jessica', 'L.', 'Jones', '8/30/88', '9/19/10 10:28:51' );

CREATE TABLE dbo.Employees
    (
        EmployeeId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,
        FirstName NVARCHAR(100) ,
        LastName NVARCHAR(100) ,
        ContactId INT NOT NULL
            CONSTRAINT FK_Employees_ContactId_ref_Contacts_ContactId
            REFERENCES dbo.Contacts ( ContactId ) ,
        ManagerId INT
    );

INSERT dbo.Employees ( FirstName ,
                       LastName ,
                       ContactId ,
                       ManagerId )
VALUES ( N'John', N'Doe', 5, NULL ) ,
       ( 'Jane', 'Smith', 6, 1 ) ,
       ( 'Henry', 'Williams', 7, 2 ) ,
       ( 'Jessica', 'Jones', 8, 3 );

CREATE TABLE dbo.Customers
    (
        CustomerId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,
        CustomerName NVARCHAR(100) ,
        Active BIT NOT NULL
            CONSTRAINT DF_Customers_Active
                DEFAULT ( 1 )
    );

INSERT dbo.Customers ( CustomerName ,
                       Active )
VALUES ( 'Bikes R'' Us', 1 ) ,
       ( 'Industrial Giant', 1 ) ,
       ( 'Widget-Works', 0 ) ,
       ( 'Custom Hangers', 1 );

CREATE TABLE dbo.Products
    (
        ProductId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,
        ProductDesc VARCHAR(100) ,
        Active BIT NOT NULL
            CONSTRAINT DF_Products_Active
                DEFAULT ( 1 )
    );

INSERT dbo.Products ( ProductDesc ,
                      Active )
VALUES ( 'Gears', 1 ) ,
       ( 'Wheels', 1 ) ,
       ( 'Widgets', 0 ) ,
       ( 'Wire', 1 );

CREATE TABLE dbo.ProductOrders
    (
        POID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,
        ProductId INT NOT NULL
            CONSTRAINT FK_ProductOrders_ProductId_ref_Products_ProductId
            FOREIGN KEY REFERENCES dbo.Products ( ProductId ) ,
        CustomerId INT NOT NULL ,
        OrderedQuantity INT ,
        Filled BIT NOT NULL
            CONSTRAINT DF_ProductOrders_Filled
                DEFAULT ( 0 ) ,
        DateOrdered DATETIME
            CONSTRAINT DF_ProductOrders_DateOrdered
                DEFAULT ( GETDATE()) ,
        DateFilled DATETIME
            CONSTRAINT DF_ProductOrders_DateFilled
                DEFAULT ( GETDATE())
    );

INSERT dbo.ProductOrders ( ProductId ,
                           CustomerId ,
                           OrderedQuantity ,
                           Filled ,
                           DateOrdered ,
                           DateFilled )
VALUES ( 2, 1, 1000, 0, '4/16/18 8:09:13', NULL ) ,
       ( 2, 1, 500, 1, '3/27/18 17:00:21', '6/24/18 13:29:01' ) ,
       ( 3, 3, 2000, 1, '12/01/04 13:28:58', '2/19/05 19:41:42' ) ,
       ( 1, 1, 632, 0, '5/23/18 4:25:52', NULL ) ,
       ( 4, 4, 901, 0, '3/30/18 21:30:28', NULL );

/*Begin writing queries below*/

--1) 
--Write a statement that returns the id, first, and last names of contacts in the database. The results should be in reverse alphabetical order.
SELECT ContactId, FirstName, LastName 
FROM dbo.Contacts
ORDER BY FirstName, LastName DESC;

--2)
--Write a statement that returns the total quantity of products ordered per customer. Return the sum as "Total Ordered" and return the customer's id.
SELECT c.CustomerId, SUM(po.OrderedQuantity) 'Total Ordered' 
FROM dbo.Customers c /* creating c as alias for dbo.Customers */
JOIN dbo.ProductOrders po  /*creating po as alias as dbo.ProductOrders */
ON c.CustomerId = po.CustomerId
GROUP BY c.CustomerId;

--3)
--Write a statement that returns product orders that were open during April of 2018 and are still open and also returns product orders that were open during April of 2018 and are no longer open. 
--Include in the results the name of the customer that placed the order, the id for the order, and the date the order was filled.
SELECT c.CustomerName, po.POID, po.DateFilled 
FROM dbo.Customers c /* creating c as alias for dbo.Customers */
JOIN dbo.ProductOrders po  /*creating po as alias as dbo.ProductOrders */
ON c.CustomerId = po.CustomerId
WHERE ((po.DateOrdered <= '04/30/2018') AND po.Filled = 0)
OR ((po.DateOrdered <= '04/30/2018') AND po.Filled = 1);

--4)
--Return a list of customer names for those customers that have currently open orders. The list should not contain any duplicates.
SELECT DISTINCT c.CustomerName 
FROM dbo.Customers c /* creating c as alias for dbo.Customers */
JOIN dbo.ProductOrders po  /*creating po as alias as dbo.ProductOrders */
ON c.CustomerId = po.CustomerId
WHERE po.Filled = 0;

--5)
--Assume that the ordered quantity for all open purchase orders was entered as half as much as it should be. Write an update statement that will correct these orders.
UPDATE dbo.ProductOrders
SET OrderedQuantity = OrderedQuantity * 2
WHERE Filled = 0;
/* need SELECT * FROM dbo.ProductOrders; in order to show the updated table */

--6)
--In a single statement create a table that does not yet exist and store the rows affected by the update statement in question 5 in that table. Data from all columns should be stored. Name the new table whatever you like.
SELECT *
INTO dbo.UpdatedProductOrders
FROM dbo.ProductOrders
WHERE Filled = 0;
/* need SELECT * FROM dbo.UpdatedProductOrders; in order to show the updated table */

--7)
--Write a statement that creates a row representing you in the Employees table without using the VALUES key word
INSERT INTO dbo.Employees (FirstName, LastName, ContactId, ManagerId)
SELECT 'Yansen','Hadiwikarsa',4 ,NULL;

--8)
--Write a statement that creates rows for all employees in the contacts table that are not already in the employees table. Specify that their Manager is Jessica Jones.
INSERT INTO dbo.Employees (FirstName, LastName, ContactId, ManagerId)
SELECT c.FirstName, c.LastName, c.ContactId, 4 
FROM dbo.Contacts c
WHERE c.contactId NOT IN (
    SELECT e.ContactId 
    FROM dbo.Employees e 
);
SELECT * FROM dbo.Employees;

--9)
--Write a single statement that will return employees who are managers, the managers of those employees, their managers, etc.
SELECT e1.EmployeeId, e1.FirstName, e1.LastName, e1.ManagerId, e2.FirstName AS 'Manager First Name', e2.LastName AS 'Manager Last Name'
FROM dbo.Employees e1, dbo.Employees e2
WHERE e1.ManagerId = e2.EmployeeId
AND e1.EmployeeId IN (
    SELECT DISTINCT e3.ManagerId 
    FROM dbo.Employees e3
);

--10)
--Without modifying the following code:
DECLARE @StartDateInput SMALLDATETIME = '1/1/2018',
			@EndDateInput SMALLDATETIME = '1/1/2018'

--Modify the following query so that it will return contacts modified at any time on January 1st, 2018
SELECT *
FROM dbo.Contacts
WHERE CAST(ModifiedDate AS DATE) BETWEEN @StartDateInput AND @EndDateInput; /*Change ModifiedDate as Date input between declare variable assigned */


run \| edit \| history \| help	0