Program to Alter Database Tables in SQL Assignment Solution.

Instructions

Objective

Write a SQL assignment program to alter database tables in SQL language.

Requirements and Specifications

CE3.1a Change Column Name, Test Name Change, Generate Summaries:

Action	Result
alter table T_CUST_2021 rename column T_NAME to T_CNAME
desc T_CUST_2021
select O.T_ORDID, T_ORDATE, T_PID, T_QTY from T_ORD_2021 O inner join T_ORDLINE_2021 L on O.T_ORDID= L.T_ORDID
select O.T_ORDID, T_ORDATE, T_PID, T_QTY from T_ORD_2021 O left outer join T_ORDLINE_2021 L on O.T_ORDID = L.T_ORDID

select T_ORDID, T_ORDLINE, P.T_PID, T_PNAME, T_QTY from T_ORDLINE_2021 L right outer join T_PROD_2021 P on L.T_PID = P.T_PID

CE3.1b - Insert Rows, Generate Summaries:

Action	Result

select T_NAME as CName, T_ORDLINE as LNID, T_PNAME as PName, T_QTY, T_UNIT_PRICE from T_ORD_2021 O inner join T_ORDLINE_2021 L on O.T_ORDID = L.T_ORDID inner join T_CUST_2021 C on O.T_CID = C.T_CID inner join T_PROD_2021 P on L.T_PID = P.T_PID order by T_NAME, T_ORDLINE, T_PNAME

select T_NAME as CName, count(distinct T_PNAME) as No_of_Prods, sum(T_QTY *T_UNIT_PRICE) as Total_Dollars from T_ORD_2021 O inner join T_ORDLINE_2021 L on O.T_ORDID = L.T_ORDID inner join T_CUST_2021 C on O.T_CID = C.T_CID inner join T_PROD_2021 P on L.T_PID = P.T_PID group by T_NAME order by T_NAME
select sum(T_QTY * T_UNIT_PRICE) as Overall_Total_Dollars from T_ORDLINE_2021 L inner join T_PROD_2021 P on L.T_PID = P.T_PID

Source Code

-- Part 2
-- Q1
SELECT C.course_name
 FROM course_table C, registration_table R, student_table S
WHERE C.course_id = R.course_id AND R.student_id = S.student_id AND S.student_name = 'Smith';
-- Q2
SELECT F.faculty_name FROM faculty_table F
 INNER JOIN course_table C ON C.instructor_id = F.faculty_id
 INNER JOIN registration_table R ON C.course_id = R.course_id
 INNER JOIN student_table S ON R.student_id = S.student_id
WHERE S.student_name = 'Baker'
-- Q3
SELECT S.student_id, S.student_name, SUM(C.num_credits)
 FROM student_table S, registration_table R, course_table C
WHERE R.student_id = S.student_id AND C.course_id = R.course_id
GROUP BY S.student_name, S.student_id;
-- Q4
SELECT F.faculty_name
 FROM faculty_table F, course_table C, registration_table R, student_table S
WHERE
 F.faculty_id = C.instructor_id AND
 C.course_id = R.course_id AND
 R.student_id = S.student_id AND
 C.course_id = 'INFO 364' AND
 R.grade = 4;

Create a Program to Alter Database Tables in SQL Assignment Solution.

Instructions

Requirements and Specifications