Database Concepts

SELECT * FROM CUSTOMER;

SELECT * FROM EMPLOYEE;

SELECT * FROM VENDOR;

SELECT * FROM ITEM;

SELECT * FROM SALE_ITEM;

SELECT ItemID, ItemDescription FROM ITEM WHERE ItemPrice >= 1000;

SELECT ItemID, ItemDescription FROM ITEM, VENDOR WHERE ItemPrice >= 1000 AND CompanyName LIKE 'New*' AND ITEM.VendorID = VENDOR.VendorID;

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (SELECT CustomerID FROM SALE WHERE SaleID = 1);

SELECT LastName, FirstName, Phone FROM CUSTOMER AS C INNER JOIN SALE AS S ON C.CustomerID = S.CustomerID WHERE SaleID = 1;

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (SELECT CustomerID FROM SALE WHERE SaleID IN (1, 2, 3));

SELECT LastName, FirstName, Phone FROM CUSTOMER AS C INNER JOIN SALE AS S ON C.CustomerID = S.CustomerID WHERE SaleID IN (1, 2, 3);

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (SELECT CustomerID FROM SALE WHERE SubTotal > 500);

SELECT DISTINCT LastName, FirstName, Phone FROM CUSTOMER AS C INNER JOIN SALE AS S ON C.CustomerID = S.CustomerID WHERE SubTotal > 500;

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (SELECT CustomerID FROM SALE WHERE SaleID IN (SELECT SaleID FROM SALE_ITEM WHERE ItemPrice > 500));

SELECT DISTINCT LastName, FirstName, Phone FROM (CUSTOMER AS C INNER JOIN SALE AS S ON C.CustomerID = S.CustomerID) INNER JOIN SALE_ITEM AS SI ON S.SaleID = SI.SaleID WHERE ItemPrice > 500;

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (SELECT CustomerID FROM SALE WHERE SaleID IN (SELECT SaleID FROM SALE_ITEM WHERE ItemID IN (SELECT ItemID FROM ITEM WHERE VendorID IN (SELECT VendorID FROM VENDOR WHERE CompanyName LIKE 'L*'))));

SELECT DISTINCT LastName, FirstName, C.Phone FROM (((CUSTOMER AS C INNER JOIN SALE AS S ON C.CustomerID = S.CustomerID) INNER JOIN SALE_ITEM AS SI ON S.SaleID = SI.SaleID) INNER JOIN ITEM AS I ON SI.ItemID = I.ItemID) INNER JOIN VENDOR AS V ON I.VendorID = V.VendorID WHERE CompanyName LIKE 'L*';

SELECT CUSTOMER.CustomerID, LastName, FirstName, Phone, SUM(SubTotal) AS SumOfSubTotal FROM CUSTOMER INNER JOIN SALE ON CUSTOMER.CustomerID = SALE.CustomerID GROUP BY CUSTOMER.CustomerID, LastName, FirstName, Phone ORDER BY CUSTOMER.CustomerID DESC;

SELECT * FROM VENDOR;

UPDATE VENDOR SET CompanyName = 'Linens and Other Stuff' WHERE CompanyName = 'Linens and Things';

SELECT * FROM VENDOR;

UPDATE VENDOR SET CompanyName = 'Linens and Things' WHERE CompanyName = 'Linens and Other Stuff';

SELECT * FROM VENDOR;

UPDATE VENDOR SET CompanyName = 'Temp' WHERE CompanyName = 'Linens and Things';

UPDATE VENDOR SET CompanyName = 'Linens and Things' WHERE CompanyName = 'Lamps and Lighting';

UPDATE VENDOR SET CompanyName = 'Lamps and Lighting' WHERE CompanyName = 'Temp';

SELECT * FROM VENDOR;

CREATE TABLE CUSTOMER( CustomerID Int NOT NULL, LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, Address Char(35) NULL, City Char(35) NULL, State Char(2) NULL, ZIP Char(10) NULL, Phone Char(12) NOT NULL, EmailAddress VarChar(100) NULL, CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID) );

CREATE TABLE EMPLOYEE( EmployeeID Int NOT NULL, LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, Phone Char(12) NULL, EmailAddress VarChar(100) NOT NULL UNIQUE, CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeID) );

CREATE TABLE ITEM( ItemID Int NOT NULL, ItemDescription VarChar(255) NOT NULL, PurchaseDate Date NOT NULL, ItemCost Numeric NOT NULL, ItemPrice Numeric NOT NULL, VendorID Int NOT NULL, CONSTRAINT ITEM_PK PRIMARY KEY (ItemID), CONSTRAINT ITEM_VENDOR_FK FOREIGN KEY (VendorID) REFERENCES VENDOR(VendorID) );

CREATE TABLE SALE( SaleID Int NOT NULL, CustomerID Int NOT NULL, EmployeeID Int NOT NULL, SaleDate Date NOT NULL, SubTotal Numeric NULL, Tax Numeric NULL, Total Numeric NULL, CONSTRAINT SALE_PK PRIMARY KEY (SaleID), CONSTRAINT SALE_CUSTOMER_FK FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) , CONSTRAINT SALE_EMPLOYEE_FK FOREIGN KEY(EmployeeID) REFERENCES Employee(EmployeeID) );

CREATE TABLE SALE_ITEM( SaleID Int NOT NULL, SaleItemID Int NOT NULL, ItemID Int NOT NULL, ItemPrice Numeric NOT NULL, CONSTRAINT SALE_ITEM_PK PRIMARY KEY (SaleID, SaleItemID), CONSTRAINT SALE_ITEM_SALE_FK FOREIGN KEY (SaleID) REFERENCES SALE(SaleID) , CONSTRAINT SALE_ITEM_ITEM_FK FOREIGN KEY (ItemID) REFERENCES ITEM(ItemID) );

CREATE TABLE VENDOR( VendorID Int NOT NULL, CompanyName Char(100) NULL, ContactLastName Char(25) NOT NULL, ContactFirstName Char(25) NOT NULL, Address Char(35) NULL, City Char(35) NULL, State Char(2) NULL, ZIP Char(10) NULL, Phone Char(12) NOT NULL, Fax Char(12) NULL, EmailAddress VarChar(100) NULL, CONSTRAINT VENDOR_PK PRIMARY KEY(VendorID) );

ALTER TABLE CUSTOMER ADD ReferredBy Int NULL;

ALTER TABLE CUSTOMER ADD CONSTRAINT CUST_FK_CUST FOREIGN KEY(ReferredBy) REFERENCES CUSTOMER (CustomerID);

ALTER TABLE EMPLOYEE ADD Supervisor Int NULL;

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_EMP_FK FOREIGN KEY (Supervisor) REFERENCES EMPLOYEE (EmployeeID);

ALTER TABLE EMPLOYEE ADD Training Varchar(20) NULL;

ALTER TABLE EMPLOYEE ALTER COLUMN Training Varchar(20) NOT NULL;

CREATE TABLE PHONE_NUMBER ( VendorID INT NOT NULL, Phone CHAR(12) NOT NULL, PhoneType CHAR(25) NULL, CONSTRAINT PHONE_NUMBER_PK PRIMARY KEY (VendorID, Phone), CONSTRAINT PHONE_NUMBER_VENDOR_FK FOREIGN KEY (VendorID) REFERENCES VENDOR (VendorID) );

ALTER TABLE VENDOR DROP COLUMN Fax;

ALTER TABLE VENDOR DROP COLUMN Phone;

INSERT INTO PHONE_NUMBER (VendorID, Phone, PhoneType) SELECT VendorID, Phone, NULL FROM VENDOR;

INSERT INTO PHONE_NUMBER (VendorID, Phone, PhoneType) SELECT VendorID, Fax, 'Fax' FROM VENDOR WHERE Fax IS NOT NULL;

SELECT CustomerID, LastName, FirstName, Phone, EmailAddress FROM CUSTOMER;

SELECT S.SaleID, SI.SaleItemID, SI.ItemID, SaleDate, ItemDescription, ItemCost, I.ItemPrice AS ListItemPrice, SI.ItemPrice AS ActualItemPrice FROM (SALE AS S INNER JOIN SALE_ITEM AS SI ON S.SaleID = SI.SaleID) INNER JOIN ITEM AS I ON SI.ItemID = I.ItemID;