Write the sql code, Database Management System

Assignment Help:

Express the following queries in SQL:

(a) Write the SQL code that will create ALL the four tables given in the appendix. Your answer should not contain the primary key and foreign key constraint declaration. The data type for each attribute should be for the SQL Server 2005 convention.

(b) Having created the table structure in question (a) above, write SQL code to make the following change in the structure of the tables:

(i) Make the PubId the Primary Key in the Publisher table

(ii) Make the ISBN the primary key and the Pub_Id the foreign key in the Book table.

(iii) Make the AU_ID and the ISBN the Primary Key of the Book_Author table.

(iv) Make the AU_ID and the ISBN the foreign key of the Book_Author table.

(v) Make the AU_ID the Primary key of the Author table

(c) After creating the table structure above, you write the following SQL code to insert the first row in the Book table:

Insert into Book

Values(‘0-0038307-6-4', ‘dBASE III : A Practical Guide', 1985, 469);

When you run the above code SQL Server shows an error message. Explain the reason for the error. How will you correct the error? Explain your answer fully.

(d) If you wanted the au_id column to be automatically incremented when inserting new record in the author table, how would you modify the create statement in question(a) above so that it starts with the number 100 and increment by one every time you create a new record in that table.

(e) Write the SQL code to change the year born value from 1970 to 1971 for the author whose ID is 1.

(f) Write the SQL code required to list all book details whose title's name contains the word "Information".


Related Discussions:- Write the sql code

State in brief about object design, Give the Introduction of OBJECT DESIGN ...

Give the Introduction of OBJECT DESIGN Strategies which are selected during system design are carried out in object-oriented design. In this procedure, objects which are identi

What are stand-alone procedures, What are stand-alone procedures? Proce...

What are stand-alone procedures? Procedures that are not part of a package are called as stand-alone because they independently defined. A good example of a stand-alone procedu

What is the need of a log in a dbms, What is the need of a log in a DBMS? B...

What is the need of a log in a DBMS? Briefly describe the various types of records that are normally present in a log.            Ans:  The system log, which is generally writt

Er-diagram, courier company er-diagram

courier company er-diagram

Select statement-data manipulation language, SELECT Statement This stat...

SELECT Statement This statement is used for retrieving information from the databases. It can be coupled with many clauses. Let us talk about these clauses in more detail:

What is entity in dbms, What is entity in DBMS? Entity: A DBMS entity i...

What is entity in DBMS? Entity: A DBMS entity is either a thing in the modelled world or a drawing element in an ERD.

Queries, Find the names of all Albums that have more than 30 tracks. Result...

Find the names of all Albums that have more than 30 tracks. Result: (name: varchar(255))

Sorting-stacks, 1. Identify the comparisons and exchanges made to sort the ...

1. Identify the comparisons and exchanges made to sort the following array using the selection exchange sort technique. Note: you may not need all columns. Ori

Develop a cards website, Project Description: I want to build a card pla...

Project Description: I want to build a card playing Website. The user should be able to 1. Login. 2. Have profile access. 3. Put money into the site. 4. Selects t

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd