· 8 years ago · May 14, 2018, 07:44 PM
1
2use ns_arh96480;
3
4insert into Building(buidling_id, city, state, zip) VALUES (001, "Athens", "GA", 30602);
5insert into Building(buidling_id, city, state, zip) VALUES (002, "Athens", "GA", 30602);
6
7insert into Building values (005,"Macon","GA",30909);
8
9insert into Building(city, state, zip, buidling_id) VALUES ("Atlanta", "GA", 30808, 006);
10
11
12Select * from Building;
13
14USE text;
15
16select * from share;
17
18
19SELECT shrfirm, shrprice
20FROM share
21WHERE shrcode ="AR" OR "BE";
22
23SELECT * FROM share
24WHERE shrcode NOT IN ("AR", "BE", "CS");
25
26use ns_arh96480;
27
28CREATE TABLE Nation(
29 natcode INT (5),
30 nationName VARCHAR(45),
31 PRIMARY KEY (natcode)
32);
33
34CREATE table Stock (
35stkcode int(5),
36stkName VARCHAR (10),
37stkPrice DECIMAL(6,2),
38acode INT (5),
39PRIMARY KEY (stkcode),
40CONSTRAINT fk_new foreign KEY (acode)
41REFERENCES Nation(natcode) ON DELETE restrict
42);
43
44SELECT *
45FROM Products;
46
47SELECT productName, productLine, (MSRP - buyPrice) as "Price Difference"
48FROM Products
49ORDER BY productLine, (SELECT (MSRP - buyPrice)) DESC;
50
51SELECT productName, productLine
52FROM Products
53WHERE productScale REGEXP "1:700";
54
55
56SELECT *
57FROM Customers;
58
59SELECT customerName, phone
60FROM Customers
61WHERE creditLimit >= (SELECT AVG(creditLimit) AS "Average Credit Limit" FROM Customers);
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79USE ns_arh96480;
80
81SELECT productName, productLine, (MSRP - buyPrice) as "Price Difference"
82FROM Products
83ORDER BY productLine, (SELECT (MSRP - buyPrice)) DESC;
84
85SELECT productName, productLine
86FROM Products
87WHERE productScale REGEXP "1:700";
88
89SELECT customerName, phone
90FROM Customers
91WHERE creditLimit >= (SELECT AVG(creditLimit) AS "Average Credit Limit" FROM Customers);
92
93
94
95
96 USE Text;
97
98SELECT * FROM stock;
99
100SELECT * FROM nation;
101
102/*
103Joining with a foreign key
104*/
105
106SELECT natname, stkfirm, stkprice,stkqty,exchrate
107FROM stock
108JOIN nation
109ON stock.natcode = nation.natcode
110WHERE stkprice >= 10
111ORDER BY stkprice;
112
113
114SELECT natname, stkfirm, (stkprice*stkqty*exchrate) AS "Stock Value"
115FROM stock
116JOIN nation
117ON stock.natcode = nation.natcode;
118
119
120
121SELECT natname, stkfirm, ROUND((stkprice*stkqty*exchrate),2) AS "Stock Value"
122FROM stock
123JOIN nation
124ON stock.natcode = nation.natcode;
125
126SELECT natname, ROUND(SUM(stkprice*stkqty*exchrate),2) AS "Stock Value"
127FROM stock
128JOIN nation
129ON stock.natcode = nation.natcode
130GROUP BY nation.natcode
131HAVING StockValue > 200000;
132
133SELECT *
134FROM Professor
135JOIN Department
136ON Department.deptID = Professor.deptID;
137
138SELECT facFname, facLname
139FROM Professor
140WHERE deptID = "ART" ;
141
142
143SELECT *
144FROM Department;
145
146SELECT deptUrl
147FROM Department
148JOIN Professor
149WHERE facID = 7890;
150
151USE Chapter4;
152
153SELECT *
154FROM Department
155JOIN Professor;
156
157SELECT deptName, COUNT(facID)
158FROM Department
159JOIN Professor
160ON Department.deptID = Professor.deptID
161GROUP BY (deptName)
162HAVING COUNT(facID)<=2;
163
164USE Text;
165
166SELECT *
167FROM stock
168WHERE stkfirm regexp '[io]nia';
169#inia or onia
170SELECT *
171FROM stock
172WHERE stkfirm regexp 'ee';
173#same as
174SELECT *
175FROM stock
176WHERE stkfirm regexp '[e]{2}';
177
178SELECT *
179FROM stock
180WHERE stkfirm regexp '^.{2}t';
181#starts with any two characters and third character is a t
182
183
184SELECT *
185FROM stock
186JOIN nation
187ON nation.natcode = stock.natcode
188WHERE stkprice>(SELECT AVG(stkprice)FROM stock);
189# all stocks who are > avg
190/*
191SELECT *
192FROM stock
193JOIN nation
194ON nation.natcode = stock.natcode
195WHERE stkprice>(SELECT AVG(stkprice)FROM stock WHERE nation.natcode = stock.natcode);
196*/
197# all stocks who are > avg from stocks in the nation (runs through each nation to find above average)
198
199
200CREATE VIEW information(firmname, stockprice)
201AS
202SELECT stkfirm, stkprice
203FROM stock;
204
205
206SELECT *
207FROM Customers
208JOIN Payments;
209
210SELECT customerName, contactFirstName,contactLastName, COUNT(checkNumber)
211FROM Customers
212JOIN Payments
213ON customers.customerNumber = Payments.customerNumber
214GROUP BY customerNumber;
215
216SELECT customerName, phone, checkNumber, amount
217FROM Customers
218JOIN Payments;
219
220
221SELECT *
222FROM Offices;
223
224SELECT city, country, COUNT(employeeNumber)
225FROM Offices
226JOIN Employees
227ON Offices.officeCode = Employees.officeCode
228GROUP BY (city);
229
230
231SELECT deptName, COUNT(facID)
232FROM Department
233JOIN Professor
234ON Department.deptID = Professor.deptID
235GROUP BY (deptName) HAVING COUNT(facID)<=2;
236
237SELECT *
238FROM Employees;
239
240SELECT customerName, AVG (amount)
241FROM Customers
242JOIN Payments
243ON Customers.customerNumber = Payments.customerNumber
244GROUP BY (customerName);
245
246
247SELECT *
248FROM Payments;
249/*
250SELECT *
251FROM stock
252JOIN nation
253ON nation.natcode = stock.natcode
254WHERE stkprice>(SELECT AVG(stkprice)FROM stock WHERE nation.natcode = stock.natcode);
255*/
256
257SELECT customerName, AVG(amount), COUNT(amount)
258FROM Customers
259JOIN Payments
260ON Customers.customerNumber = Payments.customerNumber
261WHERE (amount)>(SELECT AVG(amount) FROM Payments WHERE Customers.customerNumber = Payments.customerNumber)
262GROUP BY (customerName);
263
264
265SELECT *
266FROM item;
267
268SELECT *
269FROM sale;
270
271SELECT *
272FROM lineitem;
273
274
275SELECT *
276FROM sale
277JOIN lineitem ON sale.saleno = lineitem.saleno
278JOIN item ON item.itemno = lineitem.itemno;
279
280
281SELECT *
282FROM sale
283JOIN (item, lineitem)
284ON sale.saleno = lineitem.saleno
285AND item.itemno = lineitem.itemno;
286## TOP SAME AS BOTTOM
287SELECT * FROM Orders;
288SELECT * FROM OrderDetails;
289SELECT * FROM Products;
290
291SELECT *
292FROM Orders
293JOIN (OrderDetails, Products)
294ON Orders.orderNumber = OrderDetails.orderNumber
295AND OrderDetails.productCode = Products.productCode;
296
297
298SELECT Orders.orderNumber, COUNT(DISTINCT productVendor)
299FROM Products
300GROUP BY Orders.orderNumber;
301
302SELECT *
303FROM item, sale, lineitem
304WHERE item.itemno = lineitem.itemno
305AND lineitem.saleno = sale.saleno;
306
307#same product number of times product dought customer name and product name ascending
308SELECT * FROM Orders;
309
310
311SELECT * FROM OrderDetails;
312
313SELECT * FROM Products;
314
315SELECT customerName, productName, COUNT(Orders.orderNumber)
316FROM Customers
317JOIN (Orders, OrderDetails, Products)
318ON Customers.customerNumber = Orders.customerNumber
319AND Orders.orderNumber = OrderDetails.orderNumber
320AND OrderDetails.productCode = Products.productCode
321GROUP BY Customers.customerNumber, Products.productCode
322ORDER BY customerName AND productName DESC;
323
324SELECT customerName
325FROM Customers
326WHERE customerNumber IN (SELECT DISTINCT customerNumber FROM Orders);
327
328#W
329SELECT customerName FROM Customers
330WHERE EXISTS
331(SELECT * FROM Orders WHERE Customers.customerNumber = Orders.customerNumber);
332#As long as an order exists give me an order name
333SELECT customerName
334FROM Customers
335WHERE exists (SELECT * FROM Orders);
336
337
338# first name and last name with lastname starting with p and
339#(2nd query) all employees in the USA
340SELECT lastName, firstname
341FROM Employees
342WHERE lastName REGEXP '^P'
343union
344SELECT lastName, firstName
345FROM Employees
346JOIN Offices
347On Offices.officeCode = Employees.officeCode
348WHERE country = "USA";
349
350USE sakila;
351
352
353# name of film and number of times a film has been rented if more than 25 times
354SELECT title, COUNT(rental_id)
355FROM film
356JOIN (rental, inventory)
357ON film.film_id = inventory.film_id
358AND inventory.inventory_id = rental.inventory_id
359GROUP BY(film.film_id)
360HAVING COUNT(rental_id) >25;
361
362# number of movies that an actor has appeared in
363SELECT first_name, last_name, COUNT(film_id)
364FROM actor
365JOIN film_actor
366ON actor.actor_id = film_actor.actor_id
367GROUP BY (actor.actor_id);
368# movies whose length is above average
369SELECT title
370FROM film
371WHERE length > (SELECT AVG(length) FROM film);
372
373#longest films in each category
374SELECT title, name, length
375FROM film, film_category, category
376WHERE film.film_id = film_category.film_id
377AND category.category_id = film_category.category_id
378AND length =
379(SELECT MAX(length)
380FROM film
381JOIN (film_category)
382WHERE film.film_id = film_category.film_id
383AND category.category_id = film_category.category_id);
384
385
386
387
388
389
390
391
392
393
394
395SELECT title, MAX(length)
396FROM category
397JOIN (film_category, film)
398WHERE film.film_id = film_category.film_id
399AND filmcategory.category_id = film_category.category_id
400GROUP BY (name);
401
402
403CREATE TABLE dept(
404 deptname VARCHAR(15),
405 deptfloor SMALLINT NOT NULL,
406 deptphone SMALLINT NOT NULL,
407 empno SMALLINT NOT NULL,
408 PRIMARY KEY(deptname));
409
410CREATE TABLE emp(
411 empno SMALLINT,
412 empfname VARCHAR(10),
413 empsalary DECIMAL(7,0),
414 deptname VARCHAR(15),
415 bossno SMALLINT,
416 PRIMARY KEY(empno),
417 CONSTRAINT fk_belong_dept FOREIGN KEY(deptname)
418 REFERENCES dept(deptname),
419 CONSTRAINT fk_has_boss foreign key (bossno)
420 REFERENCES emp(empno));
421
422INSERT INTO emp (empno, empfname, empsalary, deptname,bossno)
423 VALUES (1,'Alice',75000,'Management',1);
424INSERT INTO emp VALUES (2,'Ned',45000,'Marketing',1);
425INSERT INTO emp VALUES (3,'Andrew',25000,'Marketing',2);
426INSERT INTO emp VALUES (4,'Clare',22000,'Marketing',2);
427INSERT INTO emp VALUES (5,'Todd',38000,'Accounting',1);
428INSERT INTO emp VALUES (6,'Nancy',22000,'Accounting',5);
429INSERT INTO emp VALUES (7,'Brier',43000,'Purchasing',1);
430INSERT INTO emp VALUES (8,'Sarah',56000,'Purchasing',7);
431INSERT INTO emp VALUES (9,'Sophie',35000,'Personnel',1);
432
433
434# recursive join
435SELECT *
436FROM emp
437JOIN dept
438ON dept.deptname = emp.deptname;
439# department and employee who is boss of that department (one to one)
440SELECT *
441FROM emp
442JOIN dept
443ON dept.empno = emp.empno;
444
445
446SELECT empfName
447FROM emp
448JOIN dept
449ON dept.empno = emp.empno
450WHERE dept.deptName = "Accounting";
451
452
453
454#recursive join
455SELECT supervisor.empfname
456FROM emp AS supervisor
457JOIN emp AS supervised
458ON supervisor.bossno = supervised.bossno
459WHERE supervised.empfname= 'Sarah';
460
461
462#ASSIGNMENT 3
463########################################################################################################################################################################################
464USE ClassicModels ;
465
466SELECT customerName, productName
467FROM Customers
468JOIN (Orders, OrderDetails, Products)
469ON Customers.customerNumber = Orders.customerNumber
470AND Orders.orderNumber = OrderDetails.orderNumber
471AND OrderDetails.productCode = Products.productCode
472WHERE Orders.status = "In Process";
473#Write a query to display the customer name for all customers that exist in the Customers table that have not placed any order. Order the results by customer name in the ascending order.
474SELECT customerName
475FROM Customers
476JOIN Orders
477WHERE NOT EXISTS
478(SELECT * FROM Orders WHERE Customers.customerNumber = Orders.customerNumber)
479GROUP BY(customerName);
480#Write a query to list the different offices (their address),
481#count of the number of employees at that office and
482#the number of orders that employees at that office were sales representatives for.
483
484/*
485SELECT city, country, COUNT(employeeNumber)
486FROM Offices
487JOIN Employees
488ON Offices.officeCode = Employees.officeCode
489GROUP BY (city);
490*/
491
492SELECT Offices.addressLine1, Offices.addressLine2, COUNT(DISTINCT Employees.employeeNumber), COUNT(Customers.salesRepEmployeeNumber)
493FROM Offices
494JOIN (Employees, Customers)
495ON Offices.officeCode = Employees.officeCode
496AND Employees.employeeNumber = Customers.salesRepEmployeeNumber
497GROUP BY (Offices.City);
498
499
500/*
501
502*/
503########################################################################################################################################################################################
504
505
506SELECT worker.firstName AS "Worker", boss.firstName AS "Supervisor"
507FROM Employees AS boss
508JOIN Employees AS worker
509ON boss.employeeNumber = worker.reportsTo;
510
511
512
513USE Text;
514
515
516Select * from product;
517
518
519
520SELECT * FROM assembly;
521
522
523SELECT *
524FROM assembly AS subProduct
525JOIN product AS bigProduct
526ON subProduct.prodid = bigProduct.prodid;
527
528# cost of everything that makes up animal photo kit JOIN assembly, bigproduct and subproduct
529SELECT bigProduct.proddesc, subproduct.proddesc,sum(subproduct.prodcost * assembly.quantity)
530FROM product AS bigProduct
531JOIN assembly ON bigProduct.prodid = assembly.prodid
532JOIN product AS subproduct ON subproduct.prodid = assembly.subprodid;
533
534
535USE text;
536
537
538
539SELECT * FROM product;
540
541SELECT * FROM assembly;
542
543
544SELECT mainProduct.proddesc, COUNT(subproduct.prodid)
545FROM product AS mainProduct
546JOIN assembly ON mainProduct.prodid = assembly.prodid
547JOIN product AS subproduct ON assembly.subprodid = subproduct.prodid;
548
549
550
551
552SELECT * FROM monarch;
553# predecssor of Virctoria 1
554SELECT premonname, premonnum
555FROM monarch
556WHERE monname = 'Victoria'
557AND monnum = 'I';
558
559SELECT pre_monarch.rgnbeg
560FROM monarch AS cur_monarch
561JOIN monarch AS pre_monarch ON pre_monarch.premonname = cur_monarch.monname
562AND pre_monarch.monnum = cur_monarch.monnum
563WHERE cur_monarch.monname = 'Victoria'
564AND cur_monarch.monnum = 'I';
565
566
567USE ClassicModels;
568
569
570/*
571This query returns the customer name and the a count of the number of orders they have placed
572and the Sales rep lname/fname
573for those customers whose count of orders is greater than the average number of orders placed by
574all customers.
575*/
576SELECT Customers.customerName AS 'Customer', COUNT(Orders.orderNumber) As 'Number of Orders', Employees.lastName AS 'Sales Rep LName', Employees.firstName AS 'Sales Rep FName'
577FROM Customers, Employees, Orders
578WHERE Customers.salesRepEmployeeNumber = Employees.employeeNumber
579AND Customers.customerNumber = Orders.customerNumber
580GROUP BY Customers.customerNumber
581HAVING COUNT(Orders.orderNumber) >
582(SELECT AVG(counts)
583FROM (SELECT COUNT(Orders.orderNumber) AS 'counts'
584FROM Orders , Customers
585WHERE Customers.customerNumber = Orders.customerNumber
586GROUP BY Customers.customerNumber) A )
587ORDER BY Customers.customerName;
588
589
590SELECT AVG(counts)
591FROM (SELECT COUNT(Orders.orderNumber) AS 'counts'
592FROM Orders , Customers
593WHERE Customers.customerNumber = Orders.customerNumber
594GROUP BY Customers.customerNumber) A;
595
596
597
598USE ClassicModels;
599
600/*
601This query returns the customer name and the a count of the number of orders they have placed
602and the Sales rep lname/fname
603for those customers whose count of orders is greater than the average number of orders placed by
604all customers.
605*/
606Create procedure query2()
607SELECT Customers.customerName AS 'Customer', COUNT(Orders.orderNumber) As 'Number of Orders', Employees.lastName AS 'Sales Rep LName', Employees.firstName AS 'Sales Rep FName'
608FROM Customers, Employees, Orders
609WHERE Customers.salesRepEmployeeNumber = Employees.employeeNumber
610AND Customers.customerNumber = Orders.customerNumber
611GROUP BY Customers.customerNumber
612HAVING COUNT(Orders.orderNumber) >
613(SELECT AVG(counts)
614FROM (SELECT COUNT(Orders.orderNumber) AS 'counts'
615FROM Orders , Customers
616WHERE Customers.customerNumber = Orders.customerNumber
617GROUP BY Customers.customerNumber) A )
618ORDER BY Customers.customerName;
619
620/*
621This is the subquery that returns the Average number of orders after counting the number of orders
622for each customer.
623*/
624
625SELECT AVG(counts)
626FROM (SELECT COUNT(Orders.orderNumber) AS 'counts'
627FROM Orders , Customers
628WHERE Customers.customerNumber = Orders.customerNumber
629GROUP BY Customers.customerNumber) A;
630
631call query2;
632
633#BEGIN PRACTICE EXERCISES##BEGIN PRACTICE EXERCISES##BEGIN PRACTICE EXERCISES##BEGIN PRACTICE EXERCISES##BEGIN PRACTICE EXERCISES#
634#################################################################################################################################################
635#################################################################################################################################################
636#################################################################################################################################################
637#########################################################BEGIN PRACTICE EXERCISES################################################################
638#################################################################################################################################################
639#################################################################################################################################################
640#################################################################################################################################################
641#BEGIN PRACTICE EXERCISES##BEGIN PRACTICE EXERCISES##BEGIN PRACTICE EXERCISES##BEGIN PRACTICE EXERCISES##BEGIN PRACTICE EXERCISES#
642
643
644
645
646#########################SQL Exercise #1 – Topic from Chapter 3 ###################################################
647#Q1. Write an SQL statement to list all the products (by code and name) that have a quantity in stock less than 100.
648SELECT productCode, productName
649FROM Products
650WHERE quantityInStock < 100;
651
652/*
653SELECT *
654FROM stock
655WHERE stkfirm regexp '[io]nia';
656#inia or onia
657SELECT *
658FROM stock
659WHERE stkfirm regexp 'ee';
660#same as
661SELECT *
662FROM stock
663WHERE stkfirm regexp '[e]{2}';
664
665SELECT *
666FROM stock
667WHERE stkfirm regexp '^.{2}t';
668#starts with any two characters and third character is a t
669
670*/
671#Q2. Write an SQL statement to list all the products (show all columns) associated with the product line vintage cars, planes, or trains. Order the result by product code.
672Select *
673FROM Products
674WHERE productLine IN ('Vintage Cars', 'Planes', 'Trains');
675
676
677#Q3. Write an SQL statement to calculate the average MSRP and average quantity in stock for the products within the product line motorcycles.
678SELECT avg(MSRP), avg(quantityInStock)
679FROM Products
680WHERE productLine = 'Motorcycles';
681
682#Q4. Write an SQL statement to calculate how many products are associated with the product line vintage cars or planes, and that have a quantity in stock less than 200.
683SELECT COUNT(productCode)
684FROM Products
685WHERE productLine IN ('Vintage Cars', 'Planes')
686AND quantityInStock<200;
687
688#Q5. Write an SQL statement to list each product code and its MSRP increased by 10%. Order by descending order of that amount.
689SELECT productCode, (MSRP*1.1)
690FROM Products
691ORDER BY MSRP*1.1 desc;
692
693#Q6. Write an SQL statement to list the minimum and maximum MSRP among all the products that have a product code starting by S18.
694SELECT MAX(MSRP), MIN(MSRP)
695FROM Products
696WHERE productCode regexp ('^S18');
697#WHERE productCode LIKE 'S18%'
698
699#Q7. Write an SQL statement to list all the products (by name and code) that have the word “Special†in their product name and that have a buy price of 100 at the most. Order the result in ascending order of product name.
700SELECT productName, productCode
701FROM Products
702WHERE productName LIKE '%Special%'
703AND buyPrice < 100
704ORDER BY productName;
705
706#Q8. Write an SQL statement to count the number of products that have a buy price greater than the average of all buy prices.
707SELECT COUNT(*)
708FROM Products
709WHERE buyPrice>(SELECT AVG(buyPrice)
710FROM Products);
711#Q9. Write an SQL statement to list the product (by name, code, and vendor) that have an MSRP between 100 and 200 (inclusively). Order by descending order of vendor, and if two vendors are the same, by ascending order of product code.
712SELECT productName, productCode, productVendor
713FROM Products
714WHERE MSRP >= 100
715AND MSRP <= 200
716ORDER BY productVendor desc, productCode;
717
718#Q10. Write an SQL statement to list the products (by code, name, line, and MSRP) that have an MSRP that is less than the smallest MSRP of all products from the Planes product line.
719SELECT productCode, productName, productLine, MSRP
720FROM Products
721WHERE MSRP < (SELECT MIN(MSRP) FROM Products WHERE productLine = 'Planes');
722
723
724
725
726####################SQL Exercise #2 – Topics from Chapters 3 & 4####################################
727CREATE table PRODUCT_COST(
728PC_DESC VARCHAR(20),
729PC_CODE CHAR(5),
730PC_TYPE VARCHAR(10),
731PC_AMOUNT INT,
732primary KEY(PC_CODE));
733
734INSERT INTO PRODUCT_COST(PC_DESC, PC_CODE, PC_TYPE, PC_AMOUNT) VALUES ('Direct materials', 1111, 'Variable', 50000);
735INSERT INTO PRODUCT_COST(PC_DESC, PC_CODE, PC_TYPE, PC_AMOUNT) VALUES ('Factory rent', 1112, 'FIXED', 20000);
736INSERT INTO PRODUCT_COST(PC_DESC, PC_CODE, PC_TYPE, PC_AMOUNT) VALUES ('', 1001, 'Variable', 30000);
737
738
739INSERT INTO PRODUCT_COST
740VALUES ('Depreciation', '1002', 'Fixed', 20000);
741
742INSERT INTO PRODUCT_COST
743VALUES ('Salaries', '1003', 'Hybrid', 25000);
744
745INSERT INTO PRODUCT_COST
746VALUES ('Property taxes', '1004', 'Fixed', 38000);
747
748INSERT INTO PRODUCT_COST
749VALUES ('Direct labor', '1005', 'Variable', 69000);
750
751/*Q1. Login to MySQL (http://mistsql.terry.uga.edu/). Create the table PRODUCT_COST under the database named after you. This table has 4 fields (i.e., columns):
752
753PC_DESC (type VARCHAR) (length 20) Null Allowed (this is a check box), Default is NULL
754PC_CODE (type CHAR) (length 5) primary key (under Index)
755PC_TYPE (type VARCHAR) (length 10)
756PC_AMOUNT (type INT)
757
758Then, insert the following three rows in your table (click on the Insert tab):
759
760PC_DESC PC_CODE PC_TYPE PC_AMOUNT
761Direct materials 1111 Variable 50000
762Factory rent 1112 Fixed 20000
763 1001 Variable 30000
764
765Records shown in Browse tab. Note that records are not displayed in the order in which they were entered.
766
767Then, run the following four INSERT INTO commands (copy and paste these into the SQL window and click on Go):
768
769INSERT INTO PRODUCT_COST
770VALUES ('Depreciation', '1002', 'Fixed', 20000);
771
772INSERT INTO PRODUCT_COST
773VALUES ('Salaries', '1003', 'Hybrid', 25000);
774
775INSERT INTO PRODUCT_COST
776VALUES ('Property taxes', '1004', 'Fixed', 38000);
777
778INSERT INTO PRODUCT_COST
779VALUES ('Direct labor', '1005', 'Variable', 69000);
780
781Now, browse the content of the PRODUCT_COST table. You should have the following:
782
783PC_DESC PC_CODE PC_TYPE PC_AMOUNT
784NULL 1001 Variable 30000
785Depreciation 1002 Fixed 20000
786Salaries 1003 Hybrid 25000
787Property taxes 1004 Fixed 38000
788Direct labor 1005 Variable 69000
789Direct material 1111 Variable 50000
790Factory rent 1112 Fixed 20000
791*/
792CREATE table PRODUCT_COST(
793PC_DESC VARCHAR(20),
794PC_CODE CHAR(5),
795PC_TYPE VARCHAR(10),
796PC_AMOUNT INT,
797primary KEY(PC_CODE));
798
799INSERT INTO PRODUCT_COST(PC_DESC, PC_CODE, PC_TYPE, PC_AMOUNT) VALUES ('Direct materials', 1111, 'Variable', 50000);
800INSERT INTO PRODUCT_COST(PC_DESC, PC_CODE, PC_TYPE, PC_AMOUNT) VALUES ('Factory rent', 1112, 'FIXED', 20000);
801INSERT INTO PRODUCT_COST(PC_DESC, PC_CODE, PC_TYPE, PC_AMOUNT) VALUES ('', 1001, 'Variable', 30000);
802
803
804INSERT INTO PRODUCT_COST
805VALUES ('Depreciation', '1002', 'Fixed', 20000);
806
807INSERT INTO PRODUCT_COST
808VALUES ('Salaries', '1003', 'Hybrid', 25000);
809
810INSERT INTO PRODUCT_COST
811VALUES ('Property taxes', '1004', 'Fixed', 38000);
812
813INSERT INTO PRODUCT_COST
814VALUES ('Direct labor', '1005', 'Variable', 69000);
815
816#Q2. Write an SQL statement to count: a) the total number of products in the table, and b) the number of products that do not have a description.
817SELECT COUNT(PC_CODE)
818FROM PRODUCT_COST;
819
820SELECT COUNT(*)
821FROM PRODUCT_COST
822WHERE PC_DESC = '';
823#Q3. Write an SQL statement to update all the empty (null) product description(s) with the value “Direct expensesâ€.
824
825UPDATE PRODUCT_COST
826SET PC_DESC = 'Direct expenses'
827WHERE PC_DESC = '';
828
829#Q4. Write an SQL statement to list all product cost types (in ascending order) other than Hybrid costs. Remove duplicates.
830SELECT DISTINCT PC_TYPE
831FROM PRODUCT_COST
832WHERE PC_TYPE <> 'Hybrid'
833ORDER BY PC_TYPE;
834
835#Q5. Write an SQL statement to list all the product descriptions other than Depreciation and Direct Labor.
836SELECT PC_DESC
837FROM PRODUCT_COST
838WHERE PC_DESC NOT IN ('Depreciation','Direct Labor');
839
840#Q6. Write an SQL statement to count the number of products that have an amount greater than the average of all amounts.
841SELECT COUNT(*)
842FROM PRODUCT_COST
843WHERE PC_AMOUNT>(SELECT AVG(PC_AMOUNT) FROM PRODUCT_COST);
844#Q7. Write an SQL statement to list the product descriptions (along with their codes) that have an amount between 35000 and 50000 (inclusively) and for which the cost_code includes at least one character “0†(zero).
845SELECT PC_DESC, PC_CODE
846FROM PRODUCT_COST
847WHERE PC_AMOUNT BETWEEN 35000 AND 50000
848AND PC_CODE regexp (0);
849#AND PC_CODE LIKE '%0%'
850#Q8. Write an SQL statement to delete all product costs with the type hybrid.
851DELETE FROM PRODUCT_COST
852WHERE PC_TYPE = 'Hybrid';
853#Q9. Consider the data model below. When the table MANAGER is created, how many columns will it have? How about the table PROJECT?
854# MAke sure you include the foreign key as a column when counting
855
856CREATE TABLE MANAGER(
857Mg_Name VARCHAR(15),
858Mg_Number VARCHAR(6),
859Mg_Department VARCHAR(15),
860PRIMARY KEY(Mg_Number)
861);
862
863LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/manager.csv' INTO TABLE ns_arh96480.MANAGER FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
864
865
866CREATE TABLE PROJECT (
867 P_Name VARCHAR(20),
868 P_Number VARCHAR(6) NOT NULL,
869 P_Manager VARCHAR(6),
870 P_Act_Cost INTEGER,
871 P_Exp_Cost INTEGER,
872 PRIMARY KEY(P_Number),
873 FOREIGN KEY(P_Manager) REFERENCES MANAGER(Mg_Number));
874
875
876
877LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/project.csv' INTO TABLE ns_arh96480.PROJECT FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
878
879/*
880Q10. Login to MySQL (http://mistsql.terry.uga.edu/). Create the table MANAGER under the database named after
881 you. This table has 3 fields (i.e., columns):
882
883Mg_Name (type VARCHAR) (length 15)
884Mg_Number (type VARCHAR) (length 6) primary key
885Mg_Department (type VARCHAR) (length 15)
886
887Hit the Create Table button under the list of table names on the left. Note that MySQL is case sensitive when it comes to table names. MANAGER, manager, and Manager refer to different tables.
888
889
890If you want to delete your table and start over, type the following into the SQL window to delete the table:
891
892 DROP TABLE MANAGER
893
894This drops the table named MANAGER.
895
896Then, import the file manager.csv (downloaded from eLC). The format of the imported file is “CSVâ€. Once imported, browse the table to see what is in it.
897
898
899Create the table PROJECT by executing the following SQL statement:
900
901CREATE TABLE PROJECT (
902 P_Name VARCHAR(20),
903 P_Number VARCHAR(6) NOT NULL,
904 P_Manager VARCHAR(6),
905 P_Act_Cost INTEGER,
906 P_Exp_Cost INTEGER,
907 PRIMARY KEY(P_Number),
908 FOREIGN KEY(P_Manager) REFERENCES MANAGER(Mg_Number));
909
910
911Then, import the file project.csv (downloaded from eLC). The format of the imported file is “CSVâ€. Once imported, browse the table to see what is in it.
912*/
913
914#Q11. List the project name and expected cost for the projects that have an expected cost greater than the average expected cost for all projects.
915 SELECT P_Name, P_Exp_Cost
916FROM PROJECT
917WHERE P_Exp_Cost > (SELECT avg(P_Exp_Cost) FROM PROJECT);
918
919#Q12. Calculate the average expected cost for all projects managed by the manager named Yates. (Assume that you do not know Yates’ manager number.)
920SELECT avg(P_Exp_Cost)
921FROM PROJECT, MANAGER
922WHERE PROJECT.P_Manager = MANAGER.Mg_Number
923AND Mg_Name = 'Yates';
924
925#Q13.Calculate the total actual cost for all of Kanter’s projects which are not over budget.
926SELECT SUM(P_Act_Cost)
927FROM PROJECT, MANAGER
928WHERE PROJECT.P_Manager = MANAGER.Mg_Number
929AND Mg_Name = 'Kanter'
930AND P_Act_Cost<P_Exp_Cost;
931
932#Q14. Generate a list of all projects (showing the project number and project name) that are 25% or more over budget (i.e., each project where the actual cost is at least 25% more than the expected cost).
933SELECT P_Name, P_Number
934FROM PROJECT
935WHERE P_Act_Cost>=(P_Exp_Cost*1.25);
936#Q15. List the number of projects managed by each department, for all departments except Accounting. Given the data set you are dealing with, the result of your query should be: Finance 4
937SELECT Mg_Department, COUNT(P_Number)
938FROM MANAGER, PROJECT
939WHERE PROJECT.P_Manager = MANAGER.Mg_Number
940AND Mg_Department NOT LIKE '%Accounting%'
941GROUP BY (Mg_Department);
942#HAVING Mg_Department <> 'Accounting'
943/*
944Q16. For all the managers, list his/her name, number, and the total expected costs of all his/her projects,
945only if all the manager’s projects have an expected cost greater than or equal to $1,000
946(in other words, if a manager has at least one project with an expected cost less than $1,000, don’t list this manager in your final result!).
947List the results in reverse alphabetical order of manager name. The result of the query will look like this:
948
949Kanter 1111 4500
950Baker 1002 8000
951Adams 1001 4000
952*/
953SELECT Mg_Name, Mg_Number, SUM(P_Exp_Cost)
954FROM MANAGER, PROJECT
955WHERE MANAGER.Mg_Number = PROJECT.P_Manager
956GROUP BY (Mg_Name)
957HAVING MIN(P_Exp_Cost)>1000
958ORDER BY Mg_Name desc;
959/*
960Q17. For all the managers, list his/her number and the total expected costs of all his/her projects,
961only if all the manager’s projects have an expected cost greater than or equal to $ 1,000
962(in other words, if a manager has at least one project with an expected cost less than $1,000, don’t list this manager in your final result!).
963List the results in reverse alphabetical order of manager number. The result of the query will look like this:
964
965
9661111 4500
9671002 8000
9681001 4000
969*/
970SELECT Mg_Number, SUM(P_Exp_Cost)
971FROM MANAGER, PROJECT
972WHERE MANAGER.Mg_Number = PROJECT.P_MANAGER
973GROUP BY (Mg_Name)
974HAVING MIN(P_Exp_Cost)>1000
975ORDER BY Mg_Number desc;
976#Q18.For each manager (show his/her name and number), list his/her projects (by project name), except for his/her project with the lowest actual cost.
977
978SELECT Mg_Name, Mg_Number, P_Name
979FROM PROJECT, MANAGER
980WHERE MANAGER.Mg_Number = PROJECT.P_MANAGER
981AND (SELECT MIN(P_Act_Cost)
982 FROM PROJECT
983 WHERE MANAGER.Mg_Number = PROJECT.P_Manager);
984
985
986
987CREATE TABLE WORKER (
988 w_id CHAR(6),
989 w_l_name VARCHAR(25),
990 w_hrly_rate DECIMAL(5,2),
991 w_skill_type VARCHAR(20),
992PRIMARY KEY(w_id));
993
994CREATE TABLE BUILDING (
995 bldg_id CHAR(6),
996 bldg_address VARCHAR(35),
997 bldg_type VARCHAR(20),
998 bldg_qlty_lv VARCHAR(3),
999PRIMARY KEY(bldg_id));
1000
1001CREATE TABLE ASSIGNMENT (
1002 asg_w_id CHAR(6),
1003 asg_bldg_id CHAR(6),
1004 asg_st_date DATE,
1005 asg_num_days INT,
1006 PRIMARY KEY(asg_w_id, asg_bldg_id),
1007 FOREIGN KEY(asg_w_id) REFERENCES WORKER(w_id),
1008 FOREIGN KEY(asg_bldg_id) REFERENCES BUILDING(bldg_id));
1009
1010LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/worker.csv' INTO TABLE ns_arh96480.WORKER FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1011LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/building.csv' INTO TABLE ns_arh96480.BUILDING FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1012LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/assignment.csv' INTO TABLE ns_arh96480.ASSIGNMENT FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1013
1014/*
1015SQL Exercise #3 – Topics from Chapters 3, 4 and 5
1016Please refer to the data model and tables below to answer the questions on the next page.
1017
1018I. Consider the data model below.
1019
1020How many columns will the table WORKER have?
1021How about the table ASSIGNMENT? And BUILDING?
1022How many primary key(s) will there be in the table ASSIGNMENT?
1023
1024II. Login to MySQL (http://mistsql.terry.uga.edu/). Create the tables by executing the following SQL statements:
1025
1026CREATE TABLE WORKER (
1027 w_id CHAR(6),
1028 w_l_name VARCHAR(25),
1029 w_hrly_rate DECIMAL(5,2),
1030 w_skill_type VARCHAR(20),
1031PRIMARY KEY(w_id));
1032
1033CREATE TABLE BUILDING (
1034 bldg_id CHAR(6),
1035 bldg_address VARCHAR(35),
1036 bldg_type VARCHAR(20),
1037 bldg_qlty_lv VARCHAR(3),
1038PRIMARY KEY(bldg_id));
1039
1040CREATE TABLE ASSIGNMENT (
1041 asg_w_id CHAR(6),
1042 asg_bldg_id CHAR(6),
1043 asg_st_date DATE,
1044 asg_num_days INT,
1045 PRIMARY KEY(asg_w_id, asg_bldg_id),
1046 FOREIGN KEY(asg_w_id) REFERENCES WORKER(w_id),
1047 FOREIGN KEY(asg_bldg_id) REFERENCES BUILDING(bldg_id));
1048
1049Then, import the files (downloaded from eLC). You must import the ASSIGNMENT table last!
1050The format of the imported files is ‘CSV’. Once imported, browse the tables to see what is in them.
1051 */
1052 
1053#Queries
1054
1055#1. List the weekly pay for each electrician (show the worker’s name and weekly pay) who has an assignment in a particular building. Assume a 36-hour workweek. List each worker’s name once.
1056SELECT w_l_name, (w_hrly_rate*36)AS "Worker's Weekly Pay"
1057FROM WORKER, ASSIGNMENT, BUILDING
1058WHERE WORKER.w_id = ASSIGNMENT.asg_w_id
1059AND ASSIGNMENT.asg_bldg_id = BUILDING.bldg_id
1060GROUP BY (w_l_name);
1061#2. List the skill types of workers who are assigned to building B235. Show the worker’s name and skill type. Display records in descending order by worker hourly rate.
1062SELECT w_skill_type, w_l_name
1063FROM BUILDING, ASSIGNMENT, WORKER
1064WHERE WORKER.w_id = ASSIGNMENT.asg_w_id
1065AND ASSIGNMENT.asg_bldg_id = BUILDING.bldg_id
1066AND ASSIGNMENT.asg_bldg_id = 'B235'
1067ORDER BY w_hrly_rate desc;
1068#3. List the highest and the lowest hourly wages paid to a worker.
1069SELECT MIN(w_hrly_rate), MAX(w_hrly_rate)
1070FROM WORKER;
1071#4. For the office buildings only, list the worker ID and building ID for the assignments that started after 2005/04/11.
1072SELECT asg_w_id, asg_bldg_id
1073 FROM BUILDING, ASSIGNMENT
1074WHERE asg_bldg_id = bldg_id
1075 AND bldg_type = 'Office'
1076 AND asg_st_date > '2005/04/11';
1077
1078#5. For each building quality level (BLDG_QLTY_LV), indicate the total number of workers assigned, only if this total number is greater than one.
1079SELECT BLDG_QLTY_LV, COUNT(*)
1080 FROM ASSIGNMENT, BUILDING
1081WHERE asg_bldg_id = bldg_id
1082 GROUP BY (BLDG_QLTY_LV)
1083HAVING COUNT(*)>1;
1084
1085#6. List the addresses of the buildings for which at least one assigned worker has either the ‘plumbing’ or ‘electric’ skill. Do not include any duplicates in the resulting addresses.
1086SELECT DISTINCT bldg_address
1087FROM WORKER JOIN ASSIGNMENT ON w_id = asg_w_id
1088JOIN BUILDING ON asg_bldg_id = bldg_id
1089WHERE w_skill_type IN('electric', 'plumbing');
1090#######################
1091#######NEEDS WORK######
1092#######################
1093SELECT DISTINCT BLDG_ADDRESS
1094FROM WORKER JOIN ASSIGNMENT ON w_id = asg_w_id
1095JOIN BUILDING ON asg_bldg_id = bldg_id
1096WHERE w_skill_type IN ('Plumbing', 'Electric');
1097
1098#7. List the workers who earn an hourly pay rate between $10 and $15 (inclusively) and work on an office building. Show the worker’s name and hourly pay rate. Remove duplicates.
1099SELECT DISTINCT w_l_name, w_hrly_rate
1100FROM WORKER, ASSIGNMENT, BUILDING
1101WHERE asg_bldg_id = bldg_id
1102AND w_id = asg_w_id
1103AND bldg_type = 'Office'
1104AND w_hrly_rate BETWEEN 10 AND 15;
1105
1106#8. List the assignments (by worker id, building id, building address, assignment start date, and assignment number of days) for which the assigned number of days is less than or equal to the
1107#average assigned number of days associated with the assignment’s building.
1108SELECT asg_w_id, asg_bldg_id, bldg_address, asg_st_date, asg_num_days
1109FROM ASSIGNMENT, BUILDING
1110WHERE asg_bldg_id = bldg_id
1111AND asg_num_days <=
1112(SELECT avg(asg_num_days) FROM ASSIGNMENT WHERE asg_bldg_id = BUILDING.bldg_id);
1113
1114
1115#9. Write the SQL command to delete the worker 1113 (Baker) from the WORKER table. Can this query be executed?
1116#Why (or why not?) Write the SQL command to delete the building 1112 (Boudreau) from the WORKER table.
1117#Can this query be executed?
1118DELETE FROM WORKER
1119WHERE w_id = '1112';
1120
1121
1122#10. Worker 1235 (Faraday) deserves a raise. Write the appropriate SQL command to increase his hourly rate by 3 percent.
1123UPDATE WORKER
1124SET w_hrly_rate = w_hrly_rate * 1.03
1125WHERE w_id = '1235';
1126
1127
1128
1129
1130CREATE TABLE ATHLETE (
1131 Ath_Id VARCHAR(5) ,
1132 Ath_F_Nm VARCHAR(25) ,
1133 Ath_L_Nm VARCHAR(25),
1134 Ath_Status CHAR(2),
1135 Ath_Mentor VARCHAR(5) ,
1136 Ath_Team_Id CHAR(5),
1137 PRIMARY KEY(Ath_Id));
1138
1139CREATE TABLE TEAM (
1140 Team_Id CHAR(5),
1141 Team_Nm VARCHAR(35) ,
1142 Team_Cost INTEGER,
1143 Team_Level CHAR(2),
1144 Team_Counter CHAR(5),
1145 Team_Captain VARCHAR(5),
1146 PRIMARY KEY(Team_Id));
1147LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/ATHLETE.csv' INTO TABLE ns_arh96480.ATHLETE FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1148LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/TEAM.csv' INTO TABLE ns_arh96480.TEAM FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1149
1150
1151ALTER TABLE ATHLETE
1152 ADD FOREIGN KEY (Ath_Mentor) REFERENCES ATHLETE(Ath_Id);
1153
1154ALTER TABLE ATHLETE
1155 ADD FOREIGN KEY (Ath_Team_Id) REFERENCES TEAM(Team_Id);
1156
1157ALTER TABLE TEAM
1158 ADD FOREIGN KEY (Team_Captain) REFERENCES ATHLETE(Ath_Id);
1159
1160ALTER TABLE TEAM
1161 ADD FOREIGN KEY (Team_Counter) REFERENCES TEAM(Team_Id);
1162/*
1163SQL Exercise #4 – Topics from Chapter 3, 4, 5, and 6
1164
1165
1166Answer the questions using the tables described below. Note: All columns have a text data type, except for Team_Cost, which has a numeric data type.
1167
1168Context: At the Regional Sports Center, an athlete may be part of only one team. Moreover, each team has, at the most, one athlete as its captain. It is possible for an athlete to mentor other athletes.
1169An athlete can only be mentored by one other athlete. A team can either be of level “recreational†or “competitiveâ€. A recreational team can have, at the most, one competitive counterpart (and vice-versa).
1170For example, team_id “F111†(“Washington Nationalsâ€, $90, competitive) has a recreational counterpart (team_id “F123â€, “Washington Nationals Youngâ€, $55, recreational).
1171
1172
1173ATHLETE Table
1174Primary key: Ath_id
1175Foreign keys: Ath_Team_Id (from TEAM)
1176 Ath_Mentor (from ATHLETE)
1177
1178Ath_Id Ath_F_Nm Ath_L_Nm Ath_Status Ath_Mentor (FK) Ath_Team_Id (FK)
117911113 Don Aase J 11121 B490
118011115 Andy Abad S 99999 F111
1181Etc. Etc. Etc. Etc. Etc. Etc.
1182
1183
1184TEAM Table
1185Primary key: Team_Id
1186Foreign keys: Team_Counter (from TEAM)
1187 Team_Captain (from ATHLETE)
1188
1189Team_Id Team_Nm Team_Cost Team_Level Team_Counter (FK) Team_Captain (FK)
1190B002 Baltimore Little Canaries 100 R B490 11112
1191B490 Baltimore Canaries 200 C B002 11121
1192Etc. Etc. Etc. Etc. Etc. Etc.
1193
1194To create the above tables, login to MySQL (http://mistsql.terry.uga.edu/). Create the table ATHLETE under the
1195database named after you.
1196
1197CREATE TABLE ATHLETE (
1198 Ath_Id VARCHAR(5) ,
1199 Ath_F_Nm VARCHAR(25) ,
1200 Ath_L_Nm VARCHAR(25),
1201 Ath_Status CHAR(2),
1202 Ath_Mentor VARCHAR(5) ,
1203 Ath_Team_Id CHAR(5),
1204 PRIMARY KEY(Ath_Id));
1205
1206Import the file ATHLETE.csv (downloaded from eLC) in the table ATHLETE. Then, create the table TEAM:
1207
1208CREATE TABLE TEAM (
1209 Team_Id CHAR(5),
1210 Team_Nm VARCHAR(35) ,
1211 Team_Cost INTEGER,
1212 Team_Level CHAR(2),
1213 Team_Counter CHAR(5),
1214 Team_Captain VARCHAR(5),
1215 PRIMARY KEY(Team_Id));
1216
1217Import the file TEAM.csv (downloaded from eLC) in the table TEAM. Then, indicate the following FKs:
1218
1219ALTER TABLE ATHLETE
1220 ADD FOREIGN KEY (Ath_Mentor) REFERENCES ATHLETE(Ath_Id);
1221
1222ALTER TABLE ATHLETE
1223 ADD FOREIGN KEY (Ath_Team_Id) REFERENCES TEAM(Team_Id);
1224
1225ALTER TABLE TEAM
1226 ADD FOREIGN KEY (Team_Captain) REFERENCES ATHLETE(Ath_Id);
1227
1228ALTER TABLE TEAM
1229 ADD FOREIGN KEY (Team_Counter) REFERENCES TEAM(Team_Id);
1230*/
1231
1232#Q1. Write the SQL statement to list the id and name of each of the senior athletes who mentor at least one junior athlete. Remove duplicates.
1233SELECT DISTINCT MENTOR.ATH_ID, MENTOR.ATH_F_NM, MENTOR.ATH_L_NM
1234FROM ATHLETE MENTOR, ATHLETE MENTEE
1235WHERE MENTEE.ATH_MENTOR = MENTOR.ATH_ID AND
1236MENTOR.ATH_STATUS = 'S' AND
1237MENTEE.ATH_STATUS = 'J';
1238
1239#Q2. Write the SQL statement to list the recreational teams (by ID and name) that start with “F†and that have a competitive counterpart that costs less.
1240SELECT REC.Team_Nm, REC.Team_Id
1241FROM TEAM COMP, TEAM REC
1242WHERE REC.Team_Level = 'R'
1243AND REC.Team_Id LIKE 'F%'
1244AND REC.Team_Counter = COMP.Team_Id
1245AND REC.Team_Cost > COMP.Team_Cost;
1246
1247#Q3. Write the SQL statement to list the id and name of each of the athletes who are mentors and who are part of a recreational team. Remove duplicates.
1248SELECT DISTINCT MENTOR.Ath_Id, MENTOR.Ath_F_Nm, MENTOR.Ath_L_Nm
1249FROM ATHLETE MENTOR, ATHLETE MENTEE, TEAM
1250WHERE MENTEE.Ath_Mentor = MENTOR.Ath_Id
1251AND MENTOR.Ath_Team_Id = TEAM.Team_Id
1252AND TEAM.Team_Level LIKE 'R';
1253
1254
1255SELECT MENTOR.ATH_ID, MENTOR.ATH_L_NM
1256FROM ATHLETE MENTOR, ATHLETE MENTEE, TEAM
1257WHERE MENTEE.ATH_MENTOR = MENTOR.ATH_ID
1258AND MENTOR.ATH_TEAM_ID = TEAM_ID
1259AND TEAM_LEVEL = 'R';
1260
1261#Q4. Write the SQL statement to list the id and name of all of the athletes who are the captain of a team and who are being mentored by “Andy Abadâ€. Order in descending order of athlete last name, and when the same last names are encountered, by ascending order of athlete id.
1262SELECT MENTEE.ATH_ID, MENTEE.ATH_L_NM
1263FROM ATHLETE AS MENTOR, ATHLETE AS MENTEE, TEAM
1264WHERE MENTEE.ATH_MENTOR = MENTOR.ATH_ID
1265AND Team_Captain = MENTEE.Ath_Id
1266AND MENTOR.Ath_F_Nm = "Andy"
1267AND MENTOR.Ath_L_Nm = "Abad";
1268#Q5. Write the SQL statement to list the teams (by ID, name, and level) that have a cost less than $60 (if recreational) or less than $100 (if competitive).
1269SELECT Team_Id, Team_Nm, Team_Level
1270FROM TEAM
1271WHERE ((Team_Level = 'R' AND Team_cost <60) OR (Team_Level = 'C' AND Team_cost <100));
1272#Q6. Write the SQL statement to list the competitive teams (by ID and name) that have a recreational counterpart which has a senior captain.
1273SELECT COMP.Team_Id, COMP.Team_Nm
1274FROM TEAM AS COMP, TEAM AS REC, ATHLETE
1275WHERE REC.Team_Level = 'R'
1276AND COMP.Team_Level = 'C'
1277AND COMP.Team_Counter = REC.Team_Id
1278AND REC.Team_Captain = Ath_Id
1279AND Ath_Status = 'S';
1280
1281
1282
1283
1284
1285
1286
1287
1288CREATE TABLE DEPARTMENT (
1289 deptID VARCHAR(5) ,
1290 deptName VARCHAR(35) ,
1291 PRIMARY KEY(deptID));
1292LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/DEPARTMENT.csv' INTO TABLE ns_arh96480.DEPARTMENT FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1293
1294CREATE TABLE COURSE (
1295 courseID VARCHAR(10) ,
1296 courseDesc VARCHAR(35) ,
1297 courseCredit INTEGER(1),
1298 PRIMARY KEY(courseID));
1299LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/COURSE.csv' INTO TABLE ns_arh96480.COURSE FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1300
1301CREATE TABLE OFFERING (
1302 DeptID VARCHAR(5),
1303 CourseID VARCHAR(10) ,
1304 PRIMARY KEY(DeptID, CourseID));
1305LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/OFFERING.csv' INTO TABLE ns_arh96480.OFFERING FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1306
1307ALTER TABLE OFFERING ADD FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(deptID);
1308
1309CREATE TABLE PREREQ (
1310 courseID VARCHAR(10),
1311 prereqID VARCHAR(10),
1312 duration_validity INTEGER,
1313PRIMARY KEY(courseID, prereqID));
1314
1315LOAD DATA LOCAL INFILE '/Users/andrewhall/Desktop/Year 3/MIST 4610/Practice Exercises/PREREQ.csv' INTO TABLE ns_arh96480.PREREQ FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
1316
1317
1318
1319ALTER TABLE PREREQ ADD FOREIGN KEY (courseID) REFERENCES COURSE(CourseId);
1320
1321ALTER TABLE PREREQ ADD FOREIGN KEY (prereqID) REFERENCES COURSE(CourseId);
1322/*
1323SQL Exercise #5 – Topics from Chapter 3, 4, 5, and 6
1324
1325Answer the questions using the tables below. Note: Attributes COURSECREDIT and DURATION_VALIDITY have a number data type, and all other attributes have a text data type. DURATION_VALIDITY refers to the time period (counted in number of semesters) during which a student must have taken the prerequisite in order for it to be considered as valid.
1326Context: At the International University, a course may be offered by one or many departments. Moreover, a course may have 0, 1, or many other courses as prerequisites, and courses may be prerequisites for more than one other course.
1327
1328
1329COURSE Table
1330Primary key: CourseID
1331Foreign keys: none
1332courseID courseDesc courseCredit
1333CSCI1111 Intro to Computers 3
1334EC101 Intro to Electronic Commerce 3
1335Etc. Etc. Etc.
1336
1337DEPARTMENT Table
1338Primary key: DeptID
1339Foreign keys: none
1340deptID deptName
1341CS Computer Science
1342FIN Finance
1343Etc. Etc.
1344
1345OFFERING Table
1346Primary key: DeptID & CourseID
1347Foreign keys: DeptID (references the DEPARTMENT table)
1348 CourseID (references the COURSE table)
1349DeptID (FK) CourseID (FK)
1350CS CSCI1111
1351MIS CSCI1111
1352MIS EC101
1353MKT EC101
1354Etc. Etc.
1355
1356PREREQ Table
1357Primary key: CourseID & PrereqID
1358Foreign keys: CourseID (references the COURSE table)
1359 PrereqID (references the COURSE table)
1360
1361courseID (FK) prereqID (FK) Duration_validity
1362EC205 EC101 3
1363EC101 MA1111 6
1364EC101 MIS001 6
1365Etc. Etc. Etc.
1366
1367To create the above tables, login to MySQL (http://mistsql.terry.uga.edu/). Create the table DEPARTMENT under the
1368database named after you.
1369
1370CREATE TABLE DEPARTMENT (
1371 deptID VARCHAR(5) ,
1372 deptName VARCHAR(35) ,
1373 PRIMARY KEY(deptID));
1374
1375Import the file DEPARTMENT.csv (downloaded from eLC) in the table DEPARTMENT.
1376Then, create the table COURSE:
1377
1378CREATE TABLE COURSE (
1379 courseID VARCHAR(10) ,
1380 courseDesc VARCHAR(35) ,
1381 courseCredit INTEGER(1),
1382 PRIMARY KEY(courseID));
1383
1384Import the file COURSE.csv (downloaded from eLC) in the table COURSE.
1385Then, create the table OFFERING:
1386
1387CREATE TABLE OFFERING (
1388 DeptID VARCHAR(5),
1389 CourseID VARCHAR(10) ,
1390 PRIMARY KEY(DeptID, CourseID));
1391
1392Import the file OFFERING.csv (downloaded from eLC) in the table OFFERING.
1393Then, execute the following SQL statements one by one.
1394
1395ALTER TABLE OFFERING ADD FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(deptID);
1396ALTER TABLE OFFERING ADD FOREIGN KEY (CourseID) REFERENCES COURSE(courseID);
1397
1398Then, create the table PREREQ:
1399
1400CREATE TABLE PREREQ (
1401 courseID VARCHAR(10),
1402 prereqID VARCHAR(10),
1403 duration_validity INTEGER,
1404PRIMARY KEY(courseID, prereqID));
1405
1406Import the file PREREQ.csv (downloaded from eLC) in the table PREREQ. Then, execute the following SQL statements one by one.
1407
1408ALTER TABLE PREREQ ADD FOREIGN KEY (courseID) REFERENCES COURSE(CourseId);
1409
1410ALTER TABLE PREREQ ADD FOREIGN KEY (prereqID) REFERENCES COURSE(CourseId);
1411*/
1412
1413
1414
1415
1416
1417#Q1. Write the SQL statement to generate the list of all prerequisite IDs for the class “Corporate Finâ€.
1418SELECT prereqID
1419FROM COURSE
1420JOIN PREREQ ON PREREQ.courseID = COURSE.CourseID
1421AND COURSE.courseDesc = 'Corporate Fin';
1422
1423#Q2. Write the SQL statement to generate the description of all prerequisites for “FINANCE2â€.
1424SELECT P.courseDesc
1425FROM COURSE C JOIN PREREQ ON C.courseID = PREREQ.courseID
1426JOIN COURSE P ON PREREQ.prereqID = P.courseID
1427WHERE C.courseDesc = 'FINANCE2';
1428
1429#Q3. Write the SQL statement to generate the list of course descriptions and course credits of all courses that have “FINANCE2†as a prerequisite.
1430SELECT C.courseDesc, C.courseCredit
1431FROM COURSE C JOIN PREREQ ON C.courseID = PREREQ.courseID
1432JOIN COURSE P ON PREREQ.prereqID = P.courseID
1433WHERE P.courseDesc = 'FINANCE2';
1434
1435#Q4. Write the SQL statement to generate the list of department names that do not offer any classes.
1436SELECT deptName
1437FROM DEPARTMENT
1438WHERE deptID NOT IN (SELECT deptID FROM OFFERING);
1439
1440#Q5. Write the SQL statement to generate the course description of each course that is being offered by at least two departments. Also indicate the number of departments that are in charge of each course.
1441SELECT courseDesc, COUNT(deptID)
1442FROM COURSE JOIN OFFERING
1443ON COURSE.courseID = OFFERING.CourseID
1444GROUP BY courseDesc
1445HAVING COUNT(deptID)<1;
1446
1447#final answer below but won't run because of FK constraint
1448SELECT COURSEDESC, COUNT(DEPTID)
1449FROM COURSE JOIN OFFERING
1450ON COURSE.COURSEID = OFFERING.COURSEID
1451GROUP BY COURSEDESC
1452HAVING COUNT(DEPTID) > 1;
1453#Q6. Write the SQL statement to generate the descriptions of all Finance courses that do not require any prerequisite. Assume that you do not know the deptid for the Finance department.
1454SELECT courseDesc
1455FROM DEPARTMENT JOIN OFFERING ON DEPARTMENT.deptID = OFFERING.DeptID
1456JOIN COURSE ON OFFERING.CourseID = COURSE.courseID
1457WHERE deptName = 'FINANCE'
1458AND COURSE.COURSEID NOT IN (SELECT COURSEID FROM PREREQ);
1459
1460#Q7. Write the SQL statement to generate the list of course descriptions of all the courses that have prerequisite(s) worth more than 3 credits. Do not include duplicates.
1461SELECT DISTINCT C.COURSEDESC
1462FROM COURSE C JOIN PREREQ ON C.COURSEID = PREREQ.COURSEID JOIN COURSE P ON PREREQ.PREREQID = P.COURSEID
1463WHERE P.COURSECREDIT > 3
1464
1465
1466
1467#Assignment 1
1468#QUERY A
1469#List product names, product line and the difference between the MSRP and purchase(buy) price of the products. Order the results by product line and the descending order of the difference. (Products table)
1470SELECT productName, productLine, (MSRP -buyPrice)
1471FROM Products
1472ORDER BY productLine, (MSRP- buyPrice) DESC;
1473
1474#QUERY B
1475#List product names and the product line for all products where the scale of the products is 1:700. (Products table)
1476
1477SELECT productName, productLine
1478FROM Products
1479WHERE productScale = '1:700';
1480
1481#QUERY C
1482#List the customer name and number for customers who have a greater than average credit limit. (Customers table)
1483SELECT customerName, customerNumber
1484FROM Customers
1485WHERE creditLimit > (SELECT AVG(creditLimit) FROM Customers);
1486
1487#Assignment 2
1488#QUERY A
1489#Write a query to display the city and country where an office is located and a count of the number of employees at that locaGon. Order the results in descending order of the number of employees.
1490SELECT city, country, COUNT(Employees.employeeNumber)
1491FROM Offices
1492JOIN Employees ON Offices.officeCode = Employees.officeCode
1493GROUP BY Offices.officeCode
1494ORDER BY COUNT(Employees.employeeNumber) DESC;
1495
1496#QUERY B
1497#Write a query to display the customer name and their average payment amount (Payments table) only if the customer’s average amount paid is greater than the average payment of all customers. Order the results by the customer name.
1498SELECT customerName, AVG(amount)
1499FROM Customers
1500JOIN Payments ON Customers.customerNumber = Payments.customerNumber
1501GROUP By Customers.customerNumber
1502HAVING AVG(amount) > (SELECT AVG(amount) FROM Payments);
1503
1504#QUERY C
1505#Write a query to display the customer name, the average payment amount and the number of payments they have made that is greater than their average payment amount. Order the results by the descending number of payments.
1506SELECT customerName, AVG(amount), COUNT(Payments.checkNumber)
1507FROM Customers
1508JOIN Payments ON Customers.customerNumber = Payments.customerNumber
1509WHERE amount > (SELECT AVG(amount) FROM Payments WHERE Customers.customerNumber = Payments.customerNumber)
1510GROUP By Customers.customerNumber;
1511
1512
1513
1514#Assignment 3
1515
1516USE ClassicModels;
1517
1518#QUERY A
1519#Write a query to list the customer names and the product names for those products whose Orders.status (status) is ‘In Process’.
1520SELECT c.customerName, p.productName
1521FROM Products AS p, OrderDetails AS od, Orders AS o, Customers AS c
1522WHERE p.productCode = od.productCode AND od.orderNumber = o.orderNumber
1523AND c.customerNumber = o.customerNumber
1524AND o.status = 'In Process';
1525
1526#QUERY B
1527#Write a query to display the customer name for all customers that exist in the Customers table that have not placed any order. Order the results by customer name in the ascending order.
1528
1529SELECT c.customerName FROM Customers AS c
1530WHERE NOT EXISTS (SELECT DISTINCT(o.customerNumber)
1531FROM Orders AS o WHERE c.customerNumber = o.customerNumber)
1532ORDER BY c.customerName ASC;
1533
1534#QUERY C
1535#Write a query to list the different offices (their address), count of the number of employees at that office and the number of orders that employees at that office were sales representatives for.
1536SELECT addressLine1, addressLine2, city, COUNT(Customers.customerNumber), COUNT(DISTINCT(Employees.employeeNumber))
1537FROM Offices
1538JOIN Employees ON Offices.officeCode = Employees.officeCode
1539JOIN Customers ON Employees.employeeNumber = Customers.salesRepEmployeeNumber
1540GROUP By Offices.officeCode;