————————————————-
Laboratory Experiment 4 and 5
————————————————-
Querying Relational Databases Using Advanced SQL
1. List all details from the publishers table i.e. select all columns from publishers.
2. List all details from the authors table.
3. List the pub_id for each publisher.
4. Select authors’ firstname and lastnane who live in Oakland. You will need to place the word Oakland in inverted commas ‘’.
5. List the firstnames of authors who live in California.
6. List the phone number of the author who lives in Oakland and whose lastname is ‘Green’.
7. List the publishers with pub_id not equal to 0803.
8. List the authors who do not live in California.
9. Form the Cartesian Product of the publishers and authors tables returning all columns. What result would you expect? ***//Table was too big to copy.
10. Form the Cartesian Product of the publishers and authors tables and return the pub_id column from the publishers table.
11. List all authors’ last names. Eliminate any duplicates in the results.
12. Form the Cartesian Product of the publishers and authors tables and return the pub_id column from the publishers table. Eliminate duplicate tuples.
The Essay on Table of Specifications
A Table of Specifications is a blueprint for an objective selected response assessment. The purpose is to coordinate the assessment questions with the time spent on any particular content area, the objectives of the unit being taught, and the level of critical thinking required by the objectives or state standards. The use of a Table of Specifications is to increase the validity and quality of ...
13. List the titles that are of type “business”.
14. List those psychology books with a pub_id not equal to 0803.
15. List the authors who live in California (CA), Kansas (KS) or Michigan (MI).
16. List the authors with phone numbers not beginning with “456”.
17. Find the authors with a six-letter firstname beginning with the letter M (use the underscore character).
18. Find all business and psychology books that have an advance over $5,500.
19. Find those titles with an advance between 4095 and 12000.
20. Find the total sales for each publisher.
21. List the total number of books for each author.
22. List the titles published by “New Age Books” and the price of each title.
23. List the authors and the city where they live, for those authors who live in the same city as a publisher.
24. Which author has the highest number of books as a single author.
25. List the author id’s with their titles and royalty split (royaltyper) for royalty splits less than 60.
