SQL queries and procedures
TABLE: Employees
Business Rules:
EmployeeID is defined as the primary key.
Address has been denormalized to include City and State for performance reasons.
DeptNbr is a foreign key that references DeptNbr in the Department table Manager is a foreign key that references EmployeeID in the Employees table
Data Structure:
(EmployeeId, LastName, FirstName, Street, City, State, Zip, DateOfHire, DateOfBirth, JobTitle, Salary, DeptNbr(fk), Manager(fk))
TABLE: Department
Business Rules:
DeptNbr is defined as the primary key.
Data Structure:
(DeptNbr, DeptName, DeptFax, DeptPhone)
TABLE: Customer
Business Rules:
CustID is defined as the primary key.
Address has been denormalized to include City and State for performance reasons.
Data Structure:
(CustId , CustLastName, CustFirstName, Street, City, State, Zip, CreditLimit, CurrentBalance)
TABLE: Product
Business Rules:
ProductNbr is defined as the primary key.
Data Structure:
(ProductNbr, ProductName, ProductLocation, QtyOnHand,ReorderQty, Cost, MSRP)
TABLE: Calls
Business Rules:
CallID is defined as the primary key.
CustID is a foreign key that references CustID in the Customer table.
ProductNbr is a foreign key that references ProductNbr in the Product table
EmployeeID is a foreign key that references EmployeeID in the Employee table
Data Structure:
(CallID, Date, ProblemDescr, Resolution, CustID(fk), ProductNbr(fk), EmployeeID(fk))
TABLE: Sales
Business Rules:
SalesOrderNbr is defined as the primary key.
CustID is a foreign key that references CustID in the Customer table.
EmployeeID is a foreign key that references EmployeeID in the Employee table.
Data Structure:
(SalesOrderNbr, CustID(fk),EmployeeID(fk),Date)
TABLE: SaleProduct
Business Rules:
SalesOrderNbr + ProductNbr is defined as a composite primary key.
SalesOrderNbr is a foreign key that references SalesOrderNbr in the Sales table.
ProductNbr is a foreign key that references ProductNbr in the Product table.
Data Structure: (SalesOrderNbr(fk), ProductNbr(fk),Qty,UnitPrice)
REQUIRED QUERIES:
Create each of the following queries. After the query has been written and tested, create a stored procedure for the query. Name of query/stored procedure is given in ( ) at beginning of query.
1. (EmpList) For each employee, list the name (first and last), job title . Sort the employees in order by last name.
2. (CustomerCredit) List all customers who have a balance greater than their credit limit. For each customer, list customer ID, last name, and amount over credit limit (use an alias for this column)
3. (TopProduct) Using the Sale_Product table, list the Product Number of the best selling product. "Best selling" refers to the product that has the most total quantity sold.
4. (SaleData) For each Sale, list the sale number, sale date and customer last name. (Inner Join)
5. (DeptEmployees) For each department, list the department name, and the names of employees in that department. List ALL departments, even if the department does not have any employees. (Outer Join)
1. Create Procedure EmpList
AS
Select FirstName, Lastname, JobTitle From Employees
order By LastName;
2. Create Procedure CustomerCredit
as
Select CustID, CustLastName, CurrentBalance - CreditLimit as "
Amount Over Credit Limit" from Customer
where currentbalance > creditlimit
3. Create Procedure TopProduct
as
Select ProductNbr from SaleProduct order by Qty desc
4. Create Procedure SaleData
as
Select SalesOrderNbr, Date, CustLastName from sale Inner Join customer on sale.custID=Customer.CustID
5. Create Procedure DeptEmployees
as
select d.DeptName, e.FirstName, e.LastName
from Department d join Employees e
on Department.DeptNbr(+) = Employees.DeptNbr
SQL queries and procedures TABLE: Employees Business Rules: EmployeeID is defined as the primary key. Address...
Write SQL Queries Given the following tables (7pts) Retrieve the names of employees and their project name. If the employee never worked on a project, show the names only the name, not the project name. (7pts) Retrieve the names of employees who have worked on the same project at a different location. (7pts) Retrieve the names of employees who have worked on more than two different projects. (7pts) Retrieve the names of employees who manage more than two employees. CREATE...
Please help I promise positive feedback Part IV. Formulate SQL queries that follow for the database with the following tables: CUSTOMER, PRODUCT, SALES, and ITEM-SOLD. Table: CUSTOMER Primary Key: Account-No Account-No Customer-Name Customer-City 1 A NYC 2 B NYC 3 C MIA Table: PRODUCT Primary Key: Item-No Item-No Price 1 $1.00 2 $2.00 3 $3.00 4 $4.00 Table: SALES Primary Key: Receipt-No Foreign Key: Account-No References CUSTOMER Receipt-No Account-No Sales-Person 1 1 S1 2 1 S1 3 2 S2 4...
SQL Queries – in this assignment you will be asked to create several SQL queries relating to the Sakila database that we installed in class. You may freely use DBeaver to create these queries, but I will expect your solution to show me the SQL code to answer or complete the tasks below. Write a query that produces the last name, first name, address, district, and phone number for every customer in the customer table. (You don’t need to include...
(Using Oracle SQL) How do I find the primary key of this table? And then how do I consequently drop the primary key from the table? Edit: I was already give this code and it did not work select constraint_name,constraint_type from user_constraint where table_name='CUSTOMER'; Q7 (10 Points) Use ONE SQL statement to find out the name of the primary key constraint defined on Customer table. (Your SQL should return something like SYS_C0021715, numbers on your account may differ.) Then use...
I have already turned in my HW, I need some understanding of the below SQL queries. 1. Find the number of employees in each department. 2. List the names of departments that have more than 5 employees working there. 3. Retrieve the lowest and highest salary in each department. Output the department name in alphabetical order. Consider the following relational schema. An employee can work in more than on department; also, the percentTime field of the Works relations shows the...
Create the database and tables for the database. Show all SQL statements. Include primary and foreign keys. Insert data into each table. Show select statements and display the output of each table. Note:Student’s name must be inserted into table as part of the data! Perform the SQL below: Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and...
Sample data is provided for the database for the sales system. Using the sample data, you will determine the entities, key components of the entities, and business rules for the entities. Using the entities and business rules you will then create an ERD. Tasks: 1. For each entity provide the name, description, fields, data type, primary key, and foreign key. 2. For each direct entity type pair, provide the business rules. 3. Provide the ERD. Customer Table Customer ID, Last...
CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK'); INSERT INTO DEPT VALUES (20,'HOME','DALLAS'); INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO'); INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON'); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER, SAL FLOAT, COMM FLOAT, DEPTNO INTEGER NOT NULL, FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), FOREIGN KEY (MGR) REFERENCES EMP(EMPNO), PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL, 5000,NULL,10); INSERT INTO...
I need to use SQL in microsoft access to deal with those problems. Have no ideal on how to edit the code. Here is the requirement. Database Design and Relationships 1. Create Primary Key(s) as appropriate for all tables. In TIMESHEETS, employees can only log time to a given WORKCODE once per day (e.g., if someone works on R&D for 2 hours in the morning and 2 hours in the afternoon, it's entered on the timesheet as 4 hours for...
create table candidate ( cand_id varchar(12) primary key, -- cand_id name varchar(40) -- cand_nm ); create table contributor ( contbr_id integer primary key, name varchar(40), -- contbr_nm city varchar(40), -- contbr_city state varchar(40), -- contbr_st zip varchar(20), -- contbr_zip employer varchar(60), -- contbr_employer occupation varchar(40) -- contbr_occupation ); create table contribution ( contb_id integer primary key, cand_id varchar(12), --...