forked from ta-data-lis/lab-mysql-first-queries
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathLAB sql.sql
More file actions
38 lines (30 loc) · 2.08 KB
/
Copy pathLAB sql.sql
File metadata and controls
38 lines (30 loc) · 2.08 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
#1
SELECT labsql1.titles.title_id, labsql1.titleauthor.au_id, labsql1.publishers.pub_id, labsql1.authors.au_lname, labsql1.authors.au_fname
FROM labsql1.titles
JOIN labsql1.titleauthor ON labsql1.titles.title_id = labsql1.titleauthor.title_id
JOIN labsql1.authors ON labsql1.authors.au_id = labsql1.titleauthor.au_id
JOIN labsql1.publishers ON labsql1.publishers.pub_id = labsql1.titles.pub_id ;
#2
SELECT labsql1.titles.title, labsql1.titleauthor.au_id, labsql1.publishers.pub_name, labsql1.authors.au_lname, labsql1.authors.au_fname, COUNT(labsql1.titles.title)
FROM labsql1.titles
JOIN labsql1.titleauthor ON labsql1.titles.title_id = labsql1.titleauthor.title_id
JOIN labsql1.authors ON labsql1.authors.au_id = labsql1.titleauthor.au_id
JOIN labsql1.publishers ON labsql1.publishers.pub_id = labsql1.titles.pub_id
GROUP BY labsql1.titles.title, labsql1.titleauthor.au_id, labsql1.publishers.pub_id, labsql1.authors.au_lname, labsql1.authors.au_fname;
# 3
SELECT labsql1.titles.title, labsql1.titleauthor.au_id, labsql1.publishers.pub_name, labsql1.authors.au_lname, labsql1.authors.au_fname, COUNT(labsql1.titles.title)
FROM labsql1.titles
JOIN labsql1.titleauthor ON labsql1.titles.title_id = labsql1.titleauthor.title_id
JOIN labsql1.authors ON labsql1.authors.au_id = labsql1.titleauthor.au_id
JOIN labsql1.publishers ON labsql1.publishers.pub_id = labsql1.titles.pub_id
GROUP BY labsql1.titles.title, labsql1.titleauthor.au_id, labsql1.publishers.pub_id, labsql1.authors.au_lname, labsql1.authors.au_fname
ORDER BY labsql1.titles.title DESC
LIMIT 3;
# 4
SELECT labsql1.titleauthor.au_id, labsql1.publishers.pub_name, labsql1.authors.au_lname, labsql1.authors.au_fname, COUNT(labsql1.titles.title)
FROM labsql1.titles
LEFT JOIN labsql1.titleauthor ON labsql1.titles.title_id = labsql1.titleauthor.title_id
LEFT JOIN labsql1.authors ON labsql1.authors.au_id = labsql1.titleauthor.au_id
LEFT JOIN lab2.publishers ON lab2.publishers.pub_id = lab2.titles.pub_id
GROUP BY lab2.titles.title, lab2.titleauthor.au_id, lab2.publishers.pub_id, lab2.authors.au_lname, lab2.authors.au_fname
ORDER BY lab2.titles.title DESC;