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

2 comments: