Structured Query LanguageSolve the DDL and DML exercise
Objective: To practice the DDL and DML statements.
A department store has many sections such as Toys, Cosmetics, Clothing, Household Items, and Electronics etc. Each section has many employees. Employees can belong to only one section. In addition, each section also has a head that is responsible for the section’s performance.
The department store also has many customers who purchase goods from various sections. Customers can be of two types Regular and Ad-hoc. Regular customers get credit at the department store. Maximum credit limit allowed is Rs.10000.
The store procures goods from various suppliers. The goods are stored in a warehouse and transferred to the store as and when requirement comes up. Quantity of goods supplied cannot be less than 0 and cannot be greater than 10000 for a particular supply.
The store has a computerized system for all its operations. Given below are the tables in the database:
- Section ( SectionNo Number(2), Description Varchar2(30), Section_Head_EmpNo)
- Employees( EmpNo Number(4), First_Name Varchar2(30), Last_Name Varchar2(30), Address Varchar2(30), Grade Number(2), Salary Number(9,2), SectionNo Number(2), Date_Joined Date)
- Customers( CustNo Number(4), First_Name Varchar2(30), Last_Name Varchar2(30), CustType char(1), Credit_Limit Number(7,2), Credit_Card_No Number(16), Credit_Card_Type Varchar2(8))
- Suppliers (SuppNo Number(4), Name Varchar2(30), Address Varchar2(30), City Varchar2(30) )
- Products (ProductNo Number(4), Product_Category_Code Char(3), Description Varchar2(30), Price Number(9,2), Qty_On_Hand Number(5))
- Product_CategoryMaster(Product_Category_Code Char(3), Description Varchar2(15))
- Suppliers_Products ( SuppNo Number(4), ProductNo Number(4), Date_Supplied Date, Qty_Supplied Number(5))
- Customers_Products( TransactionID Number(4), CustNo Number(4), ProductNo Number(4), Date_of_Purchase Date, Qty_Purchased Number(5))