SQL Practice paper-4

Use tables given below for answering the questions

Tables

AUTHORS

Column Name	Data type, Width	Comments
Au_Id	Char(4)	Primary Key
Au_name	Varchar2  (40)	Not Null
Phone	Char  (12)
Street	Varchar2  (40)
City	Varchar2  (20)
State	Varchar2  (20)

PUBLISHERS

Column Name	Data type, Width	Comments
Pub_Id	Char (4)	Primary Key
Pub_name	Varchar2 (20)	Not Null
Street	Varchar2 (20)
City	Varchar2 (20)
State	Varchar2 (20)

TITLES1

Column Name	Data type, Width	Comments
Title_Id	Char (4)	Primary Key
Title_name	Varchar2 (30)	Not Null
Type	Char(12)	Not Null
Pub_Id	Char (4)	Foreign key to Publisher
Price	Number (7,2)
YTD_Sales	Number (8)	Year-to-Date Sales
PubDate	Date	Not Null

TITLE_AUTHOR

Column Name	Data type, Width	Comments
Au_Id	Char (4)	Foreign key to Authors
Title_Id	Char (4)	Foreign key to Titles
Au_Ord	Number (1)

** Au_Id and Title_Id is a composite key

Q1. Display the total no. of titles of the title type ‘technical’

Q2. Display the Au_id, Au_name, title_id, title_name, and price of all

       Authors where year of publishing of a title is greater than 2003

Q3. Display the authors who have written more than 3 titles

Q4.  Display the names of top 3 publishers. The top publishers are the one who has published maximum number of books.

Q5. Display the title id, title name, publish day from titles where publish day

     is the day of the publishing

Q6. Display all author names and their corresponding title name. If the author has not written any book, display null in the title information

Q7. Display the information of author whose title name is ‘Database ’

Q8. Display the information of authors whose publisher is ‘macgrowhill’ and the price of the title is greater than 200

Q9. Display the publishers who have published at least 1 title. Use a correlated query

Q10. Create a view containing Au_id, Au_name, city, title name, price

        And publisher name

Q11. Insert the information of titles in a title table