Foreign key job references job question insert into emp select emp
PRAC 6
TASK2
INSERT INTO EMP_1
SELECT EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE FROM employee
where EMP_NUM between 103 and 109;Question 4:
Question 8:
alter table emp_2
add EMP_PCT DECIMAL(4,2),
add PROJ_NUM varchar(3);
Question 9:
update emp_2
set emp_pct = 3.85
where emp_num = 103;
Question 14:
update emp_2
set proj_num = 25
where job_code >= 502;Question 15:
update emp_2
set proj_num = 14
where emp_hiredate < '1994-01-01';
The actual result will have 50 rows)
2. Write a query to display the checkout number, check out date, and due date for every book that
has been checked out sorted by checkout number. (See the figure below for first part of the
order by check_num
3. Write a query to display the book number, book title, and subject for every book sorted by book
ORDER BY BOOK_NUM;
4. Write a query to display the book number, title, and cost of each book sorted by book number (See the figure below for the output).
   |
  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
|---|
SELECT BOOK_NUM, BOOK_TITLE, BOOK_COST AS 'Replacement Cost'
Answer provided:
SELECT DISTINCT BOOK_YEAR
SELECT DISTINCT book_subject
in descending order and then by checkout number in ascending order (See the figure below for
first part of the output. The actual result will have 68 rows)
SELECT BOOK_YEAR, BOOK_TITLE, BOOK_SUBJECT
FROM BOOKSELECT BOOK_NUM, BOOK_TITLE, BOOK_COST FROM BOOK
WHERE BOOK_COST = 59.95
ORDER BY BOOK_NUM;
FROM book
where BOOK_subject ='Database'
Answer provided:
12. Write a query to display the book number, title, and year of all books published after 2015 and
on the “Programming” subject sorted by book number (See the figure below for the output).
ORDER BY BOOK_NUM;
13. Write a query to display the book number, title, subject, and cost for all books that are on the subjects of “Middleware” or “Cloud,” and that cost more than $70 sorted by book number (See the figure below for the output).
WHERE (BOOK_SUBJECT = 'Middleware' OR BOOK_SUBJECT = 'Cloud')
AND BOOK_COST > 70
15. Write a query to display the book number, title, and subject for all books that contain the word “Database” in the title, regardless of how it is capitalized. Sort the results by book number (See the figure below for the output).
ORDER BY BOOK_NUM;
16. Write a query to display the patron ID, first and last name of all patrons who are students, sorted by patron ID (See the figure below for first part of the output. The actual result will have 44 rowsSEL
SELECT PAT_ID, PAT_FNAME, PAT_LNAME, PAT_TYPE FROM PATRON
WHERE upper(PAT_LNAME) LIKE 'C%'
ORDER BY PAT_ID;FROM AUTHOR
WHERE AU_BIRTHYEAR IS NULL
20. Write a query to display the checkout number, book number, patron ID, check out date, and due date for all checkouts that have not yet been returned. Sort the results by book number (See the figure below for the output).
22. Write a query to display the patron ID, book number, and days kept for each checkout. “Days Kept” is the difference from the date on which the book is returned to the date it was checked out. Sort the results by days kept in descending order, then by patron ID, and then by book number. (See the figure below for the output. The actual result will have 68 rows)
SELECT PAT_ID AS PATRON, BOOK_NUM AS BOOK,
datediff(CHECK_IN_DATE, CHECK_OUT_DATE) AS "Days Kept"
Answer provided:
SELECT PAT_ID, CONCAT(PAT_FNAME, ' ', PAT_LNAME) AS "Patron Name", PAT_TYPE
SELECT BOOK_NUM, concat(BOOK_TITLE, ' ','(',BOOK_YEAR,')') AS BOOK, BOOK_SUBJECT FROM BOOK
ORDER BY BOOK_NUM;ORDER BY PAT_TYPE, PAT_LNAME, PAT_FNAME;
