Question

Information on the following Database:

create database Sales_Co

use Sales_Co

create table Vendor

(v_code integer,

v_name varchar(35) not null,

v_contact varchar(15) not null,

v_areacode char(3) not null,

v_phone char(8) not null,

v_state char(2) not null,

v_order char(1) not null,

primary key (v_code));

create table product

(p_code varchar(10) constraint product_p_code_pk primary key,

p_descript varchar(35) not null,

p_indate datetime not null,

p_qoh integer not null,

p_min integer not null,

p_price numeric (8,2) not null,

p_discount numeric (4,2) not null,

v_code integer,

constraint product_v_code_fk foreign key (v_code) references vendor);

create table Customer

(cus_code integer primary key,

cus_lname varchar(15) not null,

cus_fname varchar(15) not null,

cus_initial char(1),

cus_areacode char(3),

cus_phone char(8) not null,

cus_balance numeric (9,2) default 0.00,

constraint cus_ui1 unique(cus_lname,cus_fname,cus_phone));

create table Invoice

(inv_number integer primary key,

cus_code integer not null references customer(cus_code),

inv_date datetime default current_timestamp not null,

constraint inv_ck1 check (inv_date > '2010-01-01'));

create table Line

(inv_number integer not null,

line_number numeric(2,0) not null,

p_code varchar(10) not null,

line_units numeric(9,2) default 0.00 not null,

line_price numeric(9,2) default 0.00 not null,

primary key (inv_number,line_number),

foreign key (inv_number) references invoice on delete cascade,

foreign key (p_code) references product(p_code),

constraint line_ui1 unique(inv_number,p_code));

create table Employee

(emp_num integer primary key,

emp_title char(10),

emp_lname varchar(15) not null,

emp_fname varchar(15) not null,

emp_initial char(1),

emp_dob datetime,

emp_hire_date datetime,

emp_years integer,

emp_areacode char(3),

emp_phone char(8));

create table EMP

(emp_num integer primary key,

emp_title char(10),

emp_lname varchar(15) not null,

emp_fname varchar(15) not null,

emp_initial char(1),

emp_dob datetime,

emp_hire_date datetime,

emp_areacode char(3),

emp_phone char(8),

emp_mgr integer);

2. Formulate SQL queries to run against Sales_Co database.

   1. Use the Employee table in the SaleCo database to prepare a list of all employees whose last names start or begin with ‘Smith’. The list should display employee number, first name, last name, and middle initial. The list should be alphabetized by last name. (5 points)

   2. Prepare a list of all employees who have been with the company for at least 15 years or less than 5 years. Display employee number, first name, last name, employee initial based on first and last names (call this column “Initial”), and number of years the employee has been with the company. Initial column values should have the following format: firstnameinitial.lastnameinitial. For example, the initial for George Kolmycz will be G.K. The list should be arranged from the least number of years to the most. (8 points)

   3. Prepare a list of all customers where customer owe a balance. The list should display customer code, first name, last name, and balance they owe (cus_balance). Arrange the list in ascending order by last name and then descending order by first name. (7 points)

   4. Now, modify the above query to show only the 3rd and 4th rows of your results. (4 points)

   5. Prepare a list of items purchased on each invoice. The list should include invoice number, product code of purchased item, quantity purchased, unit price and total amount (units*price) paid for that item. The list should include items from all invoice numbers except 1003, 1006, 1008. (hint: use Line table) (7 points)

   6. Now, modify above query and show top 5 rows of results only. (2 points)

   7. Prepare a list similar to the one in (5) above but include all invoice numbers here. Now, display only those items with total amount paid between $25.00 and $35.00, inclusive. (6 points)

   8. Which invoices were issued to customer # 10014 before January 17, 2018. List invoice number, and date issued. (6 points)

Answer 1

1) Use the Employee table in the SaleCo database to prepare a list of all employees whose last names start or begin with ‘Smith’. The list should display employee number, first name, last name, and middle initial. The list should be alphabetized by last name. (5 points):

Answer:

select emp_num,emp_fname,emp_lname,emp_initial from Employee where emp_lname like 'Smith%' or emp_lname like '%Smith' order by emp_lname;

2)Prepare a list of all employees who have been with the company for at least 15 years or less than 5 years. Display employee number, first name, last name, employee initial based on first and last names (call this column “Initial”), and number of years the employee has been with the company. Initial column values should have the following format: firstnameinitial.lastnameinitial. For example, the initial for George Kolmycz will be G.K. The list should be arranged from the least number of years to the most. (8 points)

Answer:

select emp_num,emp_fname,emp_lname,SUBSTRING(emp_fname,1,1)+SUBSTRING(emp_lname,1,1) as "Initial" ,emp_years from employee order by emp_years asc;

3) Prepare a list of all customers where customer owe a balance. The list should display customer code, first name, last name, and balance they owe (cus_balance). Arrange the list in ascending order by last name and then descending order by first name. (7 points):

Answer:

select cus_code,cus_fname,cus_lname,cus_balance from customer order by cus_lname asc, cus_fname desc;

4)Now, modify the above query to show only the 3rd and 4th rows of your results. (4 points):

Answer:

select cus_code,cus_fname,cus_lname,cus_balance from customer limit 2 offset 2 ;

(Note:Here Limit will allows you to display only 2 values and offset will display the 2 values after 2nd row)

5)Prepare a list of items purchased on each invoice. The list should include invoice number, product code of purchased item, quantity purchased, unit price and total amount (units*price) paid for that item. The list should include items from all invoice numbers except 1003, 1006, 1008. (hint: use Line table) (7 points)

Answer:

select inv_number,p_code,line_units,line_price,(line_units*line_price) as "total amount" from Line where inv_number NOT IN(1003, 1006, 1008);

6) Now, modify above query and show top 5 rows of results only. (2 points):

Answer:

select inv_number,p_code,line_units,line_price,(line_units*line_price) as "total amount" from Line where inv_number NOT IN(1003, 1006, 1008) LIMIT 5;

7)Prepare a list similar to the one in (5) above but include all invoice numbers here. Now, display only those items with total amount paid between $25.00 and $35.00, inclusive. (6 points)

Answer:

select inv_number,p_code,line_units,line_price,(line_units*line_price) as "total_amount" where total_amount between "$25.00" and "$35.00";

8)Which invoices were issued to customer # 10014 before January 17, 2018. List invoice number, and date issued. (6 points)   

Answer:

select inv_number from Invoice where cus_code=10014 and inv_date<"2017-01-17";

​​​​​​​

I hope you find this helpful, if not please comment below i will help you!!

Please do not forget to UpVote the answer!!

Happy Learning!!