SQL

Structured Query Language

1.   What is SQL? What are the two major categories of SQL commands? Explain them.

Ans: SQL stands for structured query language. It is a language that can be used for retrieval and management of data stored in relational database. It is a non-procedural language as it specifies what is to be retrieved rather than how to retrieve it. It can be used for defining the structure of data, modifying data in the database and specifying the security constraints.

The two major categories of SQL commands are Data Definition Language (DDL) and Data Manipulation Language (DML). DDL provides commands that can be used to create, modify and delete database objects. DML provides commands that can be used to access and manipulate the data, that is, to retrieve, insert, delete and update data in a database.

2.   What is data type? What are the various data types supported by standard SQL?

Ans: Data type identifies the type of data to be stored in an attribute of a relation and also specifies associated operations for handling the data. The common data types supported by standard SQL are as follows:

 NUMERIC(p, s) : used to represent data as floating-point number. The number can have psignificant digits (including sign) and s number of the p digits can be present on the right of decimal point. For example, the data type specified as NUMERIC(5, 2) indicates that the value of an attribute can be of form 332.32.

 INT or INTEGER: used to represent data as a number without a decimal point.

 SMALLINT: used to represent data as a number without a decimal point. It is a subset of the INTEGER; so the default size is usually smaller than INT.

 CHAR(n) or CHARACTER(n) : used to represent data as a fixed-length string of characters of size n. In case of fixed-length strings, a shorter string is padded with blank characters to the right. For example, if the value ABC is to be stored for an attribute with data type CHAR(8), the string is padded with five blanks to the right.

 VARCHAR(n) or CHARACTER VARYING: used to represent data as a variable length string of characters of maximum size n. In case of variable length string, a shorter string is not padded with blank characters.

 DATE and TIME: used to represent data as date or time. The DATE data type has three components, namely year, month and day in the form YYYY-MM-DD. The TIME data type also has three components, namely hours, minutes and seconds in the form HH:MM:SS.

 BOOLEAN: used to represent the third value unknown, in addition to true and false values, because of the presence of null values in SQL.

 TIMESTAMP: used to represent data consisting of both date and time. The TIMESTAMP data type has six components, year, month, day, hour, minute and second in the form YYYY-MM-DD-HH:MM:SS [.sF], where F is the fractional part of the second value.

3.   Which command is used for creating user-defined data types?

Ans: The user-defined data types can be created using the CREATE DOMAIN command. For example, to create user-defined data type Vchar, the command can be specified as

CREATE DOMAIN Vchar AS VARCHAR (15);

Vchar can be used as data type for any attribute for which data type VARCHAR (15) is to be defined.

4.   Explain the CREATE TABLE and DESCRIBE command.

Ans: The CREATE TABLE command is used to define a new relation, its attributes and its data types. Various constraints like key, entity integrity and referential integrity constraints can also be specified. The syntax for CREATE TABLE command is shown here.

CREATE TABLE <table_name> (<attribute1><data_type1> [constraint1],
                           <attribute2><data_type2> [constraint2],
                           :
                           <attributen><data_typen> [constraintn],
                           [table_constraint1]
                           :
                           [table_constraintn]
                         );

where table_name is the name of new relation, attributei the attribute of relation, data_typeithe data type of values of the attribute, constrainti any of the column-level constraints defined on the corresponding attribute and table_constrainti any of the table-level constraints.

For example, the command to create BOOK relation whose schema is BOOK(ISBN, Book_title, Category, Price, Copyright date, Year, Page count, P ID) can be specified as

CREATE TABLE BOOK (     ISBN            VARCHAR(15),
                        Book_title      VARCHAR(50),
                        Category        VARCHAR(20),
                        Price           NUMERIC(6,2),
                        Copyright_date  NUMERIC(4),
                        Year            NUMERIC(4),
                        Page_count      NUMERIC(4),
                        P_ID            VARCHAR(4)
                  );

The DESCRIBE (or DESC) command is used to view the structure of an already existing relation. For example, the command to view the structure of the BOOK relation can be specified as

DESCRIBE BOOK
OR
DESC BOOK

5.   What is the use of constraints? What are the different types of constraints that can be specified? Explain with examples.

Ans: Constraints are required to maintain the integrity of the data, which ensures that the data in the database are consistent, correct and valid. The different types of constraints that can be specified while creating a relation in SQL are PRIMARY KEY Constraint, UNIQUE Constraint, CHECK Constraint, NOT NULL Constraint and FOREIGN KEY Constraint.

 PRIMARY KEY constraint: This constraint ensures that the attribute declared as a primary key cannot have a null value, and no two tuples can have the same value for a primary key attribute. In other words, the values in the primary key attribute are not null and are unique. If a primary key has a single attribute, the PRIMARY KEY can be applied as a column-level as well as table-level constraint. For example, the attribute ISBN of the BOOK relation can be declared as a primary key as

ISBN VARCHAR (15) PRIMARY KEY
OR
PRIMARY KEY (ISBN)

If a primary key has more than one attribute, the PRIMARY KEY constraint is specified as a table-level constraint. For example, attributes ISBN and R_ID of the REVIEW relation can be declared as a composite primary key as

PRIMARY KEY (ISBN, R_ID)

 UNIQUE constraint: This constraint ensures that the set of attributes has unique values, that is, no two tuples can have the same value in the specified attributes. UNIQUE constraint, like the primary key can be applied as a column-level as well as table-level constraint. For example, the UNIQUE constraint on attribute Pname of the relation PUBLISHER can be specified as

Pname VARCHAR (50) UNIQUE
OR
UNIQUE (Pname)

When a UNIQUE constraint is applied on more than one attribute, it is specified as a table-level constraint. For example, the UNIQUE constraint on attributes Pname and Address of relation PUBLISHER can be specified as

UNIQUE (Pname, Address)

 CHECK constraint: This constraint ascertains that the value inserted in an attribute must satisfy a given expression. In other words, it is used to specify the valid values for a certain attribute. For example, the CHECK constraint for ensuring that the value of attribute Price of the relation BOOK is greater than $20 can be specified as

Price NUMERIC(6,2) CHECK (Price > 2 0)

The CHECK constraint when specified as a table-level constraint can be given a separate name that allows referring to the constraint whenever needed. For example, the constraint on the attribute Priceof the BOOK relation can be given a name as

CONSTRAINT Chk_price CHECK (Price > 20)

Constraints can also be applied on more than one attribute simultaneously. For example, the constraint that the Copyright_date must be either less than or equal to the Year (publishing year) can be specified as

CONSTRAINT Chk_date CHECK (Copyright_date <= Year)

 NOT NULL constraint: This constraint is used to specify that an attribute will not accept null values. For example, the NOT NULL constraint for the attribute Page_count of BOOK relation can be specified as

Page_count Numeric (4) NOT NULL

The NOT NULL constraint can be specified only as a column-level constraint and not as a table-level constraint. This constraint can also be specified using the CHECK constraint.

 FOREIGN KEY constraint: This constraint ensures that the foreign key value in the referencing relation must exist in the primary key attribute of the referenced relation, that is, foreign key references the primary key attribute of a referenced relation. For example, the attribute P_ID of the relation BOOK can be specified as a foreign key, which refers to the primary key P_ID of relation PUBLISHER as

P_ID VARCHAR(4) REFERENCES PUBLISHER(P_ID)

When FOREIGN KEY constraint is applied on more than one attribute, it is specified as a table-level constraint. For example, attributes ISBN and R_ID of relation REVIEW can be declared as foreign keys as

FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN)
FOREIGN KEY (R_ID) REFERENCES AUTHOR(A_ID)

6.   Write appropriate DDL commands to define PUBLISHER, BOOK, AUTHOR, AUTHOR_BOOK and REVIEW relations of the Online Book database.

Ans: DDL commands to define PUBLISHER, BOOK, AUTHOR, AUTHOR_BOOK and REVIEW relations of Online Book database are as follows:

CREATE TABLE PUBLISHER
      (    P_ID            VARCHAR(4),
           Pname           VARCHAR(50)     NOT NULL,
           Address         VARCHAR(50),
           State           VARCHAR(15),
           Phone           VARCHAR(20),
           Email_id        VARCHAR(30),
           PRIMARY KEY(P_ID)
      );
CREATE TABLE BOOK
      (    ISBN            VARCHAR(15),
           Book_title      VARCHAR(50)     NOT NULL,
           Category        VARCHAR(20),
           Price           NUMERIC(6,2),
           Copyright_date  NUMERIC(4),
           Year            NUMERIC(4),
           Page_count      NUMERIC(4),
           P_ID            VARCHAR(4)      NOT NULL,
           CONSTRAINT Chk_price CHECK (Price BETWEEN 20 AND 200),
           PRIMARY KEY(ISBN),
           FOREIGN KEY(P_ID) REFERENCES PUBLISHER(P_ID)
     );

CREATE TABLE AUTHOR
      (   A_ID            VARCHAR(4),
          Aname           VARCHAR(30)      NOT NULL,
          State           VARCHAR(15),
          City            VARCHAR(15),
          Zip             VARCHAR(10),
          Phone           VARCHAR(20),
          URL             VARCHAR(30),
          PRIMARY KEY(A_ID)
      );
CREATE TABLE AUTHOR_BOOK
      (   A_ID            VARCHAR(4)       NOT NULL,
          ISBN            VARCHAR(15)      NOT NULL,
          FOREIGN KEY(A_ID) REFERENCES AUTHOR(A_ID),
          FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN)
      );
CREATE TABLE REVIEW
      (    R_ID            VARCHAR(4)      NOT NULL,
           ISBN            VARCHAR(15)     NOT NULL,
           Rating          NUMERIC(2),
           PRIMARY KEY(R_ID, ISBN),
           CONSTRAINT Chk_rating CHECK (Rating BETWEEN 1 AND 10),
           FOREIGN KEY(R_ID) REFERENCES AUTHOR(A_ID),
           FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN)
      );

7.   Explain the following commands with examples:
(a) ALTER TABLE (b) DROP TABLE (c) SELECT

Ans(a): This command is used to make changes in the structure of a relation in the form of adding a new attribute, redefining an attribute or dropping attributes from a relation. The ALTER TABLEcommand can be used for the following purposes:

 to add an attribute

 to modify the definition of an attribute

 to drop an attribute

 to add a constraint

 to drop a constraint

 to rename an attribute and relation

Adding an attribute

The new attribute can be added to the relation by using the ADD clause of the ALTER TABLE command. For example, the command to add a new attribute Pname in the relation BOOK can be specified as

ALTER TABLE BOOK ADD Pname VARCHAR(10);

Modifying an attribute

Any attribute of a relation can be modified by using the MODIFY clause of the ALTER TABLEcommand. It can be used to change either its data type or size or both. The attribute to be modified must be empty before modification. For example, the command to change the data type and size of the attribute Pname can be specified as

ALTER TABLE BOOK MODIFY Pname VARCHAR(50);

Dropping an attribute

Sometimes, it is required to remove an attribute, which is no longer required from a relation. The DROP COLUMN clause of the ALTER TABLE command can be used to remove undesirable attributes from a relation. For example, the command to remove the attribute Pname from the relation BOOK can be specified as

ALTER TABLE BOOK DROP COLUMN Pname;

Adding a constraint

Different types of constraints can be added to the definition of an already existing relation. For example, the commands to add different types of constraints for the different attributes of the BOOKrelation are as follows:

ALTER TABLE BOOK ADD CHECK(Book_title <> '');
ALTER TABLE BOOK ADD CONSTRAINTCons_1 UNIQUE (ISBN);
ALTER TABLE BOOK ADD FOREIGN KEY(P_ID) REFERENCES PUBLISHER;

Dropping a constraint

Any of the constraint defined can be dropped, provided it is given a name when specified. For example, the constraint Cons_1 specified on attribute ISBN can be dropped as

ALTER TABLE BOOK DROP CONSTRAINT Cons_1;

In addition, the DEFAULT and NOT NULL constraint can be dropped as

ALTER TABLE BOOK ALTER COLUMN Category DROP DEFAULT;
ALTER TABLE BOOK ALTER COLUMN Page_count DROP NOT NULL;

Renaming an attribute

The name of an attribute of a relation can be modified by using the RENAME COLUMN … TO clause. For example, the command to modify the name of an attribute Page_count can be specified as

ALTER TABLE BOOK RENAME COLUMN Page_count TO P_count;

Renaming a relation

In addition to renaming an attribute, the name of a relation can also be modified using the RENAME TOclause. For example, the command to rename the relation BOOK to a new name can be specified as

ALTER TABLE BOOK RENAME TO Book_detail;

Ans(b): This command is used to remove an already existing relation, which is no more required as a part of a database. For example, the command to remove the relation Book_detail can be specified as

DROP TABLE Book_detail;

The two clauses that can be used with the DROP TABLE command are CASCADE and RESTRICT. If the CASCADE clause is used, all constraints and views that reference the relation to be removed are also dropped automatically. On the other hand, the RESTRICT clause, prevents a relation to be dropped if it is referenced by any of the constraints or views. These clauses can be used with the DROP TABLEcommand as

DROP TABLE Book_detail CASCADE;
DROP TABLE Book_detail RESTRICT;

Ans(c): This command is used to retrieve a subset of tuples or attributes from one or more relations. For example, the command to retrieve the attributes ISBN, Book_title and Category from the relation BOOK can be specified as

SELECT ISBN, Book_title, Category FROM BOOK;

Note that the order of the attributes appearing in the command can be different from the order of attributes in the relation. When all the attributes of a relation has to be retrieved, then instead of specifying a list of all attributes in the SELECT command, the symbol asterisk (*) denoting ‘all attributes’ can be used as

SELECT * FROM BOOK;

To eliminate duplicate tuples from the resultant relation, the keyword DISTINCT is used in the SELECT command. Hence, the command to display only the unique values for the attribute Categorycan be specified as

SELECT DISTINCT Category FROM BOOK;

The SELECT command can be used to perform simple numeric computations on the data stored in a relation. SQL allows using scalar expressions and constants along with the attribute list. For example, the command to display the incremented value of the price of books by 10% along with the attributes Book_title, Category and Price can be specified as

SELECT Book_title, Category, Price, Price+Price*.10
FROM BOOK;

WHERE clause: The WHERE clause is used when we want to retrieve the tuples based on a certain condition. A particular tuple is retrieved only if it satisfies the condition in the WHERE clause.

For example, the command to retrieve the attributes Book_title, Category and Price of those books from the BOOK relation whose Category is Novel can be specified as

SELECT Book_title, Category, Price FROM BOOK
WHERE Category = ‘Novel’;

8.   Explain the concept of aliasing and tuple variables in SQL.

Ans: The attributes of a relation can be given an alternate name using an AS clause in a SELECTcommand. Note that the alternate name is provided within the query only. For example, consider the command given here.

SELECT Book_title AS Title, P_ID AS Publisher_ID
FROM BOOK;

In this command, the attribute Book_title is renamed as Title and P_ID as Publisher_ ID. An AS clause can also be used to define tuple variables. A tuple variable is associated with a particular relation and is defined in the FROM clause. For example, to retrieve details of publishers publishing books of the language book category, the command can be specified as

SELECT P.P_ID, Pname, Address, Phone
FROM BOOK AS B, PUBLISHER AS P
WHERE P.P_ID = B.P_ID AND Category = ‘Language Book’;

In this command, tuple variables B and P are defined, which are associated with the relations BOOK and PUBLISHER, respectively.

9.   What is the purpose of the BETWEEN operator and the IN operator? Explain with examples.

Ans: BETWEEN operator: The BETWEEN comparison operator can be used to simplify the condition in the WHERE clause that specifies numeric values based on ranges. For example, the command to retrieve details of all the books with a price between 20 and 30 can be specified as

SELECT * FROM BOOK
WHERE Price BETWEEN 20 AND 30;

Similarly, the NOT BETWEEN operator can also be used to retrieve tuples that do not belong to the specified range.

IN operator: The IN operator is used to specify a list of values. The IN operator selects values that match any value in a given list of values. For example, the command to retrieve the book details belonging to the categories Textbook or Novel can be specified as

SELECT *
FROM BOOK
WHERE Category IN (‘Textbook’, ‘Novel’);

Similarly, the NOT IN operator can also be used to retrieve tuples that do not belong to the specified list.

10. Which operator of SQL is used to specify string patterns in the queries? Explain in detail with examples.

Ans: In SQL, the LIKE operator is used to specify string patterns in the queries. Different string patterns are specified by using two special wildcard characters, percent (%) and underscore ( _ ). The %character is used to match the substring with any number of characters, whereas, _ is used to match the substring with only one character. Pattern matching is case-sensitive as uppercase characters are considered different from lowercase characters. Some of the examples are:

 ‘A%’ matches any string beginning with character A

 ‘%A’ matches any string ending with character A

 ‘A%A’ matches any string beginning and ending with character A

 ‘%AO%’ matches any string with character AO appearing anywhere in a string as substring.

 ‘A_ _’ matches any string beginning with character A and is followed by exactly two characters

 ‘_ _A’ matches any string ending with character A and is preceded by exactly two characters

 ‘A_ _ _D’ matches any string beginning with character A, ending with the character D and with exactly three characters in between

Consider the following queries to illustrate the use of the LIKE operator:

The command to retrieve details of all the authors, whose name begins with the characters Jo can be specified as

SELECT * FROM AUTHOR WHERE Aname LIKE ‘Jo%’;

The command to retrieve details of all the authors, whose name begins with the characters James followed by exactly five characters, can be specified as

SELECT * FROM AUTHOR WHERE Aname LIKE ‘James_ _ _ _ _’;

The special pattern characters (% and _) can be included as a literal in the string by preceding the character by an escape character. The escape character is specified after the string using the ESCAPEkeyword. Any character not appearing in a string to be matched can be defined as an escape character. For example, consider the following pattern, in which backslash (\) is used as an escape character: LIKE ‘A\%b%’ ESCAPE ‘\’, searches the strings beginning with the characters A%b

11. Explain various set operations available in SQL with examples.

Ans: In SQL, the set operations like union, intersection and difference are implemented by using UNION, INTERSECT and MINUS operations, respectively.

UNION operation: It is used to retrieve tuples from more than one relation and it also eliminates duplicate tuples. For example, the command to find the union of all the tuples with Price less than 40 and all the tuples with Price greater than 30 from the BOOK relation can be specified as

(SELECT * FROM BOOK WHERE Price<40)
UNION
(SELECT * FROM BOOK WHERE Price>30);

INTERSECT operation: It is used to retrieve common tuples from more than one relation. For example, the command to find the intersection of all the tuples with Price less than 40 and all the tuples with Price greater than 30 from the BOOK relation can be specified as

(SELECT *
FROM BOOK
WHERE Price<40)
INTERSECT
(SELECT *
FROM BOOK
WHERE Price>30);

MINUS operation: It is used to retrieve those tuples present in one relation, which are not present in other relations. For example, the command to find the difference (using MINUS operation) of all the tuples with Price less than 40 and all the tuples with Price greater than 30 from the BOOK relation can be specified as

(SELECT * FROM BOOK WHERE Price<40)
MINUS
(SELECT * FROM BOOK WHERE Price>30);

The UNION, INTERSECT and MINUS operations automatically eliminate the duplicate tuples from the result. However, if all the duplicate tuples are to be retained, the ALL keyword can be used with these operations.

12. Which clause of the SELECT command can be used to change the order of tuples in a relation? Explain with examples.

Ans: The ORDER BY clause can be used with the SELECT command to change the order of tuples in a relation. The tuples can be arranged on the basis of the values of one or more attributes.

For example, the command to display the tuples of the relation BOOK, on the basis of Price attribute can be specified as

SELECT * FROM BOOK ORDER BY Price;

By default, the ORDER BY clause arranges the tuples in an ascending order on the basis of values of the specified attribute. However, the tuples can be sorted in a descending order by using the DESCkeyword. For example, the command to sort tuples of the relation BOOK on the basis of the Priceattribute in the descending order can be specified as

SELECT * FROM BOOK ORDER BY Price DESC;

Tuples can also be sorted on the basis of more than one attribute. This can be done by specifying more than one attribute in the ORDER BY clause.

13. Explain the following commands with examples:
(a) INSERT (b) UPDATE (c) DELETE

Ans(a): This command is used to insert tuples in a relation. This command adds a single tuple at a time in a relation. The syntax to add a tuple in a relation is

INSERT INTO <table_name> [(<attribute_list>)] VALUES
(<value list>);

where value_list is the list of values to be inserted in the corresponding attributes listed in attribute list.

For example, the command to add a tuple in the relation BOOK can be specified as

INSERT INTO BOOK (ISBN, Book_title, Category, Price, Copyright_
date, Year, Page_count, P_ID)
VALUES (‘001-987-760-9’,‘C++’, ‘Textbook’, 40, 2004, 2005, 800,
‘P001’);

Tuples can be inserted into a relation by omitting the attribute list from the command. That is, the tuple can also be inserted as

INSERT INTO BOOK
VALUES (‘001-987-760-9’, ‘C++’, ‘Textbook’, 40, 2004, 2005, 800,
‘P001’);

Ans(b): This command is used to make changes in the values of attributes of the relation. The syntax for update command is

UPDATE <table_name> SET <attribute> = New_value [WHERE Clause]

The SET clause in the UPDATE command specifies the attributes to be modified and the new values to be assigned to them. The WHERE clause is also required to specify the tuples for which the attributes are to be modified, otherwise the value for all the tuples in the relation will be modified. For example, the command to modify the City to New York for the author whose name is Lewis Ian can be specified as

UPDATE AUTHOR SET City = ‘New York’
WHERE Aname = ‘Lewis Ian’;

The command to modify State and Phone for the publisher Bright Publications can be specified as

UPDATE PUBLISHER
SET State = ‘Georgia’, Phone = ‘27643676’
WHERE Pname = ‘Bright Publications’;

Ans(c): This command is used to remove tuples, which are no more required as a part of a relation. Tuples can be deleted from only one relation at a time. The syntax for the DELETE command is

DELETE FROM <table_name> [WHERE Clause]

The condition in the WHERE clause of the DELETE command is used to specify the tuples to be deleted. If the WHERE clause is omitted, all the tuples of a relation are deleted; however, the relation remains in the database as an empty relation. For example to delete those tuples from the BOOK relation, whose category is Novel, the command can be specified as

DELETE FROM BOOK WHERE Category = ‘Novel’;

14. Explain the concept of null values in SQL.

Ans: SQL allows an attribute to have null values. The null value usually represents a missing value having one of the three interpretations—value is unknown, value is not available or the attribute is not applicable for a particular tuple. However, SQL does not distinguish between the different meanings of null. The null value in an attribute for a relation can be searched using the IS NULL predicate in the WHERE clause. For example, the command to retrieve the details of publishers not having an email ID can be specified as

SELECT * FROM PUBLISHER WHERE Email_id IS NULL;

The predicate IS NOT NULL can be used to check whether an attribute contains a non-null value. For example, to retrieve the details of publishers having an email ID, the command can be specified as

SELECT * FROM PUBLISHER WHERE Email_id IS NOT NULL;

15. What is the role of aggregate functions in SQL queries?

Ans: Aggregate functions process a set of values taken as the input and return a single value as a result. The SQL provides five built-in aggregate functions, namely AVG, MIN, MAX, SUM and COUNT. The SUM and AVG functions work for numeric values only, whereas other functions can work for numeric as well as non-numeric values, like strings, date, time, etc.

For example, the command to find the average price, maximum price and minimum price of books in the BOOK relation can be specified as

SELECT AVG (Price), MAX (Price), MIN (Price) FROM BOOK;

The COUNT(*) function is used to count the total number of tuples in the resultant relation, whereas, COUNT() is used to count the number of non-null values in a particular attribute. For example, the command to find the total number of tuples in the relation PUBLISHER can be specified as

SELECT COUNT (*) FROM PUBLISHER;

To find the number of non-null values in the attribute Category of the Book relation, the command can be specified as

SELECT COUNT (Category) FROM PUBLISHER;

If duplicate values are to be eliminated, the DISTINCT keyword can be used and the command can be specified as

SELECT COUNT(DISTINCT Category) FROM BOOK;

16. Explain how the GROUP BY clause works. What is the difference between WHERE and HAVING clauses? Explain with the help of an example.

Ans: The GROUP BY clause when used in a SELECT command divides the relation into groups on the basis of values of one or more attributes. After dividing the relation into groups, the aggregate functions can be applied on the individual group independently. The aggregate functions are performed separately for each group and return the corresponding result value separately. For example, the command to calculate the average price for each category of book in the BOOK relation can be specified as

SELECT AVG (Price) FROM BOOK GROUP BY Category;

To calculate the maximum, minimum and average price of the books published by each publisher, the command can be specified as

SELECT MAX (Price), MIN (Price), AVG (Price) FROM BOOK GROUP BY P_ID;

Conditions can be placed on the groups using the HAVING clause. For example, the command to retrieve the book categories for which the number of books published is less than 5 can be specified as

SELECT Category, COUNT(*)FROM BOOK GROUP BY Category HAVING
COUNT(*)<5;

More than one condition can be specified in the HAVING clause by using logical operators.

For example, the command to retrieve the average price and the average page count for each category of books with an average price greater than 30 and an average page count less than 900 can be specified as

SELECT Category, AVG(Price), AVG(Page_count)
FROM BOOK
GROUP BY Category
HAVING AVG(Price)>30 AND AVG(Page_count)<900;

Difference between WHERE and HAVING clauses: The HAVING clause places conditions on groups, whereas the WHERE clause is used to place conditions on the individual tuples. Another difference between the WHERE and HAVING clause is that conditions specified in the WHERE clause cannot include aggregate functions, whereas the HAVING clause can.

17. How are queries based on joins expressed in SQL? Explain the various join conditions.

Ans: Queries based on joins are those queries that combine the tuples from two or more relations. In SQL, such types of queries are expressed by specifying more than one relation in the FROM clause of the SELECT command. The condition on the basis of which relations are joined is known as the join condition. For example, the command to retrieve details of both book and publishers, where P_IDattribute in both the relations has identical values can be specified as

SELECT * FROM BOOK, PUBLISHER WHERE BOOK.P_ID=PUBLISHER.P_ID;

Alias names can also be used for the relations to make the command simple as

SELECT * FROM BOOK AS B, PUBLISHER AS P WHERE B.P_ID=P.P_ID;

This type of join query in which tuples are concatenated on the basis of the equality condition is known as an equi-join query. The resultant relation of this query consists of two columns for the attribute P_ID having identical values, one from the BOOK relation and another from the PUBLISHER relation. This can be avoided by explicitly specifying the name of attributes to be included in the resultant relation. Such a type of command can be specified as

SELECT ISBN, Book_title, Price, B.P_ID, Pname
FROM BOOK AS B, PUBLISHER AS P
WHERE B.P ID=P.P ID;

As a result of this command only one column for the attribute P_ID from the relation BOOK is displayed in the result. This type of query is known as a natural join query. In addition, some additional conditions can also be given apart from the join condition to make the selection of tuples more specific.

18. Explain the concept of nested queries with examples.

Ans: When a query is defined in the WHERE clause of another query, it is known as a nested query or subquery. The query in which another query is nested is known as an enclosing query. The result returned by the subquery is used by the enclosing query for specifying the conditions. Several levels of nested queries can be defined. That is, the query can be defined inside another query a number of times. For example, the command to retrieve ISBN, Book_title and Category of a book with minimum price can be specified as

SELECT ISBN, Book_title, Category FROM BOOK
WHERE PRICE = (SELECT MIN (Price) FROM BOOK);

In this command, first the nested query returns a minimum Price from the BOOK relation, which is used by the enclosing query to retrieve the required tuples.

19. Discuss the different operators in SQL, which are used with subqueries/nested queries.

Ans: SQL provides three useful operators that are generally used with subqueries. These are ANY, ALL and EXISTS.

 The ANY operator compares a value with any of the values in a list or returned by the subquery. This operator returns a false value if the subquery returns no tuple.

For example, the command to retrieve details of books with a price equal to any of the books belonging to the Novel category can be specified as

SELECT ISBN, Book_title, Price
FROM BOOK
WHERE Price = ANY (SELECT Price
                   FROM BOOK
                   WHERE Category = ‘Novel’);

In addition to the ANY operator, the IN operator can also be used to compare a single value to the set of multiple values. For example, the command to retrieve the details of books belonging to a category with a page count greater than 300 can be specified as

SELECT *
FROM BOOK
WHERE Category IN (SELECT Category
                   FROM BOOK
                   WHERE Page_count >300);

In case the nested query returns a single attribute and a single tuple, the query result will be a single value. In such a situation, the = can be used instead of the IN operator for the comparison.

 The ALL operator compares a value to every value in a list returned by the subquery. For example, the command to retrieve details of books with price greater than the price of all the books belonging to Novel category can be specified as

SELECT ISBN, Book_title, Price
FROM BOOK
WHERE Price > ALL (SELECT Price

                   FROM BOOK
                   WHERE Category = ‘Novel’);

 The EXISTS operator evaluates to true if a subquery returns at least one tuple as a result; otherwise, it returns a false value. For example, the command to retrieve the details of publishers having at least one book published can be specified as

SELECT P_ID, Pname, Address
FROM PUBLISHER
WHERE EXISTS (SELECT *
              FROM BOOK
              WHERE PUBLISHER.P_ID = BOOK.P_ID);

On the other hand, the NOT EXISTS operator evaluates to true if a subquery returns no tuple as a result. For example, the command to retrieve the details of publishers having not published any book can be specified as

SELECT P_ID, Pname, Address
FROM PUBLISHER
WHERE NOT EXISTS (SELECT *
                  FROM BOOK
                  WHERE PUBLISHER.P_ID = BOOK.P_ID);

20. What are assertions? Why are they necessary?

Ans: There are some data integrities, which cannot be specified using the constraints like PRIMARY KEY, NOT NULL, UNIQUE, CHECK, etc. Such a type of constraints can be specified using the assertions. The DBMS enforces the assertion on the database and the constraints specified in assertion must not be violated. Assertions are always checked whenever modifications are done in corresponding relation. The syntax to create an assertion can be specified as

CREATE ASSERTION <assertion_name>
CHECK (<condition>);

The assertion name is used to identify the constraints specified by the assertion and can be used for modification and deletion of assertion, whenever required. DBMS tests the assertion for its validity when it is created. An assertion is implemented by writing a query that retrieves any tuple that violates the specified condition. Then this query is placed inside a NOT EXISTS clause, which indicates that the result of this query must be empty. Hence, the assertion is violated whenever the result of this query is not empty. For example, the price of textbook must not be less than the minimum price of a novel, the assertion for this requirement can be specified as

CREATE ASSERTION price_constraint
CHECK(NOT EXISTS(
              SELECT *
              FROM BOOK
              WHERE Category = ‘Textbook’ AND
                               (Price<(SELECT MIN(Price)
                                FROM BOOK
                                WHERE Category=‘Novel’)
                                )
              )
);

21. What is the purpose of view in SQL? How can you create a view?

Ans: A view is a virtual relation, whose contents are derived from already existing relations and it does not exist in physical form. The contents of view are determined by executing a query based on any relation and it does not form the part of database schema. Each time a view is referred to, its contents are derived from the relations on which it is based. A view can be used like any other relation, which is, it can be queried, inserted into, deleted from and joined with other relations or views, though with some limitations on update operations. For example, the command to create a view containing details of books, which belong to Textbook and Language Book categories, can be specified as

CREATE VIEW BOOK_1
AS SELECT * FROM BOOK
WHERE Category IN (‘Textbook’, ‘Language Book’);

This command creates a view, named as BOOK_1, having details of books satisfying the condition specified in the WHERE clause.

Views can be based on more than one relation and such views are known as complex views. For example, the command to create a view consisting of attributes Book_title, Category, Priceand P_ID of the BOOK relation, Pname and State of the PUBLISHER relation can be specified as

CREATE VIEW BOOK_3
AS SELECT Book_title, Category, Price, BOOK.P_ID, Pname, State
FROM BOOK, PUBLISHER
WHERE BOOK.P_ID = PUBLISHER.P_ID;

22. What are the conditions under which views can be updated? How can a view be made non-updateable?

Ans: A view is updateable if it is based on a single relation and the update query can be mapped on to the already existing relation successfully under certain conditions. Any view can be made non-updateable by adding the WITH READ ONLY clause. That is, no INSERT UPDATE or DELETEcommand can be carried over that view.

23. What are stored procedures? When are they beneficial? Give the syntax and example of creating a procedure and function in SQL.

Ans: Stored procedures are procedures or functions that are stored and executed by the DBMS at the database server machine. In SQL standard, stored procedures are termed as persistent stored modules (PSM), as these procedures, like data, are persistently stored by the DBMS. Stored procedures improve performance, as these procedures are compiled only at the time of their creation or modifications. Hence, whenever these procedures are called for the execution, the time for compilation is saved.

Stored procedures are beneficial when a procedure is required by different applications located at remote sites, as stored procedures are located at the server site and can be invoked by any of the applications. This eliminates duplication of efforts in writing the SQL application logic and makes code maintenance easy.

The syntax for creating a stored procedure is given as

CREATE PROCEDURE <name> (<parameters1, …, parametersn>)
<local declarations>
<body of procedure>;

Parameters and local declarations are optional and if declared must be of valid SQL data types. Parameters declared must have one of the three modes, namely IN, OUT or INOUT specified for it. Parameters specified with the IN mode are arguments passed to the stored procedure and act like a constant, whereas OUT parameters act like an un-initialized variables and they cannot appear on the right-hand side of the = symbol. Parameters with the INOUT mode have combined properties of both the IN and OUT mode. For example, the procedure to update the value of the price of a book with a given ISBN of the relation BOOK can be specified as

CREATE PROCEDURE Update_price (IN B_ISBN VARCHAR(15),
IN New_Price NUMERIC(6,2))
UPDATE BOOK
SET Price = New_Price
WHERE ISBN = B_ISBN;

Procedures cannot return a value; thus, when a value is required to be returned to the calling program, functions are required. The syntax for creating a function is

CREATE FUNCTION <name> (<parameters1, …, parametersn>)
RETURNS <return_type>
<local_declarations>
<body of function>;

For example, the function returning the rating of a book with a given ISBN and author ID can be specified as

CREATE FUNCTION Book_rating (IN B_ISBN VARCHAR(15),
IN Au_ID VARCHAR(4))
RETURNS NUMERIC(2)
DECLARE B_rating NUMERIC(2)
SET B_rating=(SELECT Rating FROM REVIEW WHERE ISBN=B_ISBN AND
R_ID = Au_ID);
RETURN B_rating;

24. What are triggers? How are they created? Explain with an example how triggers offer a powerful mechanism for dealing with the changes to database.

Ans: A trigger is a type of stored procedure that is executed automatically when some database-related events like, insert, update, delete, etc. occur. Triggers, unlike procedures, do not accept any arguments. The main aim of triggers is to maintain data integrity and also one can design a trigger for recording information, which can be used for auditing purposes. The trigger can be created as

CREATE TRIGGER <trigger_name>
[BEFORE or AFTER][INSERT or UPDATE or DELETE]ON <relation_name>
[FOR EACH ROW]
WHEN <condition>
<statements>;

For example, when the value in the Phone attribute of a new inserted tuple of a relation AUTHOR is empty, indicating the absence of a phone number, the trigger to insert a null value in this attribute can be specified as

CREATE TRIGGER Setnull_phone BEFORE INSERT ON AUTHOR
REFERENCING NEW ROW AS nr

FOR EACH ROW
WHEN nr.Phone = ‘ ’
SET nr.Phone = NULL;

The FOR EACH ROW clause makes sure that a trigger is executed for every single tuple processed. Such a type of trigger is known as the row-level trigger, whereas the trigger that is executed only once for a specified statement, regardless of the number of tuples being effected as a result of that statement, is known as a statement-level trigger. The FOR EACH STATEMENT clause specifies the trigger as a statement-level trigger. Triggers can be enabled and disabled by using the ALTER TRIGGER command, and the triggers that are not required can be removed by using the DROP TRIGGER command.

Triggers offer a powerful mechanism for dealing with the changes made to the database as they can be used for the following purposes:

 Implementing and maintaining complex integrity constraints.

 Recording the changes for auditing purposes.

 Automatically passing a signal to other programs that action needs to be taken whenever specific changes are made to a relation.

25. What do you understand by an active database? What are the three parts of triggers?

Ans: A database having triggers associated with it is known as an active database. A trigger consists of three parts:

 Event: Any change in the database that activates the trigger.

 Condition: When the trigger is activated, the condition is checked.

 Action: A procedure that is to be executed whenever the trigger is activated and the corresponding condition is satisfied.

In other words, a trigger is an event-condition-action rule that states that whenever a specified event occurs and the condition is satisfied, the corresponding action must be executed.

26. What do you understand by an embedded SQL? How are variables declared and used in an embedded SQL? Explain with examples.

Ans: Embedded SQL refers to the use of SQL statements within a host language program. For accessing a database from any application program, the SQL statements are embedded within an application program written in host language. These statements are also known as static, that is, they do not change at the run-time.

Variables of host language can be referred in SQL statements. Such variables are also known as host variables and are prefixed by a colon (:) when they appear in SQL statements and are declared between the commands EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION.

For example, the declaration of variables corresponding to the attributes of the relation BOOK is given below:

EXEC SQL BEGIN DECLARE SECTION;
        varchar c ISBN[15], c book title[50], c category[2 0];
        char c p id[4];
        int c copyright date, c year, c page count;
        float c price;
        int SQLCODE;
        char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

Before executing any SQL statements, the host program must establish a connection with the database as

EXEC SQL CONNECT TO <server_name> AS <connection_name>
AUTHORIZATION <user_name and password>;

In this command, server_name identifies the server to which a connection is to be established and the connection_name is the name provided to the connection. In addition, user_name and password identify the authorized user. While programming, more than one connection can be established with only one connection active at a time. When a connection is no longer needed, it can be ended by using the command

EXEC SQL DISCONNECT <connection_name>;

Assuming that the required connection is already established, the command to insert a tuple in a relation using host variables can be specified as

EXEC SQL INSERT INTO BOOK
VALUES (:c ISBN, :c book title, :c category, :c price, :c copyright date, :c year, :c page count, :c p id);

27. What problem is faced while using an embedded SQL and how is it addressed by cursors? Explain with examples.

Ans: In an embedded SQL, only a single tuple can be retrieved. However, while programming, more than one tuple satisfying the condition may also be retrieved. To deal with such a situation, the concept of cursors can be used. Cursor is a mechanism that provides a way to retrieve multiple tuples from a relation and then process each tuple individually in a host program. The cursor is first opened, then processed and then closed. The cursor can be declared on any relation or any SQL statement that returns a set of tuples.

For example, the program segment to increment the price of books belonging to a given category from the relation BOOK by the value entered by the user can be written as

printf(“Enter the category of the book :”);
scanf(“%s”,c_category);
EXEC SQL DECLARE Curl CURSOR FOR
SELECT Book_title, Price, Year
FROM BOOK
WHERE Category=:c_category
ORDER BY Book_title
FOR UPDATE OF Price;
EXEC SQL OPEN Curl;
EXEC SQL FETCH FROM Curl INTO :c_Book_title, :c_Price, :c_Year
while(SQLCODE==0)
{
    printf(“Enter the increment amount :”);
    scanf(“%f”,&inc);
    EXEC SQL
        UPDATE BOOK
        SET Price=Price+ :inc
        WHERE CURRENT OF Curl;

    EXEC SQL FETCH FROM Curl INTO :c_Book_title, :c_Price,
    :c_Year;
}
EXEC SQL CLOSE Curl;

28. Differentiate between a dynamic SQL and an embedded SQL.

Ans: Dynamic SQL statements are SQL statements that are generated at run-time and it is placed as a string in the host variable.

Dynamic SQL is slower than an embedded SQL as it involves time to generate a query during run-time. However, it is more powerful than the embedded SQL, as queries can be generated at the run-time as per varying user requirements. For example, this sample program allows users to enter the update SQL query to be executed.

EXEC SQL BEGIN DECLARE SECTION;
    char sqlquerystring[200];
EXEC SQL END DECLARE SECTION;
printf("Enter the required update query : \n");
scanf("%s", sqlquerystring);
EXEC SQL PREPARE sqlcommand FROM :sqlquerystring;
EXEC SQL EXECUTE sqlcommand;

29. What is the purpose of ODBC in SQL? How is it implemented? Explain with examples.

Ans: Open Database Connectivity (ODBC) is a standard that provides an application programming interface (API), which is used by the application programs to establish a connection with the database. Once the connection is established, the application program can communicate with the database through queries.

An application program from the client site can access several DBMSs by making an ODBC API call. The requests from the client program are then processed at the server sites, and the results are sent back to the client program. Consider an example of C code using ODBC API given below:

void Example()
{
   HENV En1; //environment
   HDBC Cn; //to establish connection
   RETCODE Err;
   SQLAllocEnv(&En1);
   SQLAllocConnect(En1,&Cn);
   SQLConnect(Cn, “db.onlinebook.edu”, SQL_NTS, “John”, SQL_
   NTS, “Passwd”, SQL_NTS);
   int c_Price1, c_Price2;
   int L1, L2;
   HSTMT st;
   char *Query = “SELECT MAX(Price), MIN(Price) FROM BOOK
                   GROUP BY Category”;
   SQLAllocStmt(Cn, &st);
   Err = SQLExecDirect(st, Query, SQL_NTS);
   if (Err == SQL_SUCCESS)

   {
       SQLBindCol(st, 1, SQL_C_INT, &c_Price1, 0, &L1);
       SQLBindCol(st, 2, SQL_C_INT, &c_Price2, 0, &L2);
       while(SQLFetch(st)== SQL_SUCCESS)
       {
           printf(“Maximum Price is %d”, c_Price1);
           printf(“Minimum Price is %d”, c_Price2);
       }
   }
   SQLFreeStmt(st, SQL_DROP);
   SQLDisconnect(Cn);
   SQLFreeConnect(Cn);
   SQLFreeEnv(En1);
}

In the beginning of the program, the variables Enl, Cn and Err of types HENV, HDBC and RETCODE, respectively, are declared. The variables Enl and Cn are used to allocate an SQL environment and database connection handle, respectively. The variable Err is used for error detection. Further, the program establishes a connection with the database by using the SQLConnect() function, which accepts parameters, database connection handle (Cn), server (db.onlinebook.edu), user identifier (John) and the password (Passwd). Notice the use of constant SQL_NTS, which denotes that the previous argument is a null-terminated string.

After establishing a connection, the program communicates with the database by sending a query to the SQLExecDirect () function. The attributes of the query result are bounded to corresponding C variables by using the SQLBindCol () function. The parameters passed to the SQLBindCol () are as follows:

 First parameter (st): Stores the result of the query

 Second parameter (1 or 2): Determines the location of an attribute in the result of a query

 Third parameter (SQL_C_INT): Specifies the required data type conversion of an attribute from SQL to C.

 Fourth parameter (&c_Price1 or &c_Price2): specifies the address of the C variable where the attribute value is to be stored. Note that the values of the last two parameters passed to the SQLBindCol() function depends on the data type of an attribute. For example, in case of fixed-length types, such as float or integer the fifth parameter is ignored and the negative value in the last parameter indicates a null value in an attribute.

When the resultant tuple is fetched using the SQLFetch() function, the attribute values of the query are stored in corresponding C variables. The SQLFetch() function executes the statement st as long as it returns the value SQL_SUCCESS. In each iteration of the while loop, the attribute values for each category are stored in corresponding C variables and are displayed through printf statements. The connection must be closed when it is no more required using the SQLDisconnect() function. Also, all the resources that are allocated must be freed.

30. What is the purpose of JDBC in SQL? Explain with examples.

Ans: JDBC provides a standard API that is used to access databases, through Java, regardless of the DBMS. The four main components required for the implementation of JDBC are application, driver manager, data source-specific drivers and corresponding data sources.

An application establishes and terminates the connection with a data source. The main goal of the driver manager is to load JDBC drivers and pass JDBC function calls from the application to the corresponding driver. The driver establishes the connection with the data source. The driver performs various basic functions like submitting requests and returning results. In addition, the driver translates data, error formats and error codes from a form that is specific to the data source into the JDBC standard. The data source processes commands from the driver and returns the results.

For understanding the connectivity of the Java program with JDBC, consider the sample program segment given below:

public static void Sample(String DB_id, String U_id, String
Pword)
{
    String URL=“jdbc:oracle:oci8:@db.onlinebook.
      edu:100:onbook_db”;
    Class.forName(“oracle.jdbc.driver.OracleDriver”);
    Connection Cn=DriverManager.getConnection(URL,U_id, Pword);
    Statement st = Cn.createStatement();
    try
    {
        st.executeUpdate(“INSERT INTO AUTHOR VALUES(‘A010’,
        ‘Smith Jones’, ‘Texas’)”);
    }
    catch(SQLException se)
    {
        System.out.println(“Tuple cannot be inserted.”+se);
    }
    ResultSet rs = st.executeQuery(“SELECT Aname, State from AUTHOR
    WHERE City = ‘Seattle’”);
    while(rs.next())
    {
        System.out.println(rs.getString(1)+” “+ rs.getString(2));
    }
    st.close();
    Cn.close();
}

In this program, the following steps are taken when writing a Java application program accessing database through JDBC function calls:

1.   Appropriate drivers for the database are loaded by using Class.forName.

2.   The getConnection() function of the DriverManager class of JDBC is used to create the connection object. The first parameter (URL) specifies the machine name (db.online-book.edu)where the server runs, the port number (100) used for communication, schema (onbook_db) to be used and the protocol (jdbc:oracle:oci8) used to communicate with the database. To connect to the database, username and password are also required, which are specified by the strings U_id and Pword, respectively, the other two parameters of the getConnection() function.

3.   A statement handle is created for the connection established, which is used to execute an SQL statement. In this example, st.executeUpdate is used to execute an INSERT statement of SQL. The try {..}catch{..} is used to catch any exceptions that may arise as a result of executing this query statement.

4.   Another query is executed using the statement st.executeQuery. The result of this may consist of a set of tuples, which is assigned to rs of type ResultSet.

5.   The next() function is used to fetch one tuple at a time from the result set rs. The value of an attribute of a tuple is retrieved by using the position of the attribute. The attribute Aname is at first (1) position and State is at second (2) position. The value for the attribute can also be retrieved by using its name as shown here.

System.out.println(rs.getString("Aname")+" "
                    +rs.getString("State"));

6.   The connection must be closed after it is no more required at the end of the procedure.

31. What is SQLJ? What is the significance of using SQLJ? Explain with examples.

Ans: SQLJ is a standard that has been used for embedding SQL statements in Java programming language, which is object-oriented language. In SQLJ, a pre-processor called SQLJ translator converts SQL statements into Java, which can be executed through the JDBC interface. Hence, it is essential to install the JDBC driver while using SQLJ. When writing SQLJ applications, regular Java code is written and SQL statements are embedded into it using a set of rules. SQLJ applications are pre-processed using the SQLJ translation program that replaces the embedded SQLJ code with calls to an SQLJ library. Any Java compiler can then compile this modified program code. The SQLJ library calls the corresponding driver, which establishes the connection with the database system. The use of SQLJ improves the productivity and manageability of the JAVA code as the code becomes compact and no run-time syntax errors occur as SQL statements are checked at the compile time. Moreover, it allows sharing of Java variables with SQL statements.

For understanding the working of SQLJ, consider a sample SQLJ program segment given here. This program retrieves the book details belonging to a given category:

String Book title; float Price; String Category;
#sql iterator Bk(String Book title, float Price);
Bk book = {SELECT Book_title, Price INTO :Book_title, :Price
FROM BOOK WHERE Category = :Category};
while(book.next())
{
    System.out.println(book.Book title()+ ", " + book.Price());
}
book.close();

SQLJ statements always begin with the #sql keyword. The result of SQL queries is retrieved through the objects of an iterator, which is a type of cursor. The iterator is associated with the tuples and attributes appearing in the query result. An iterator is declared as an instance of iterator class.

32. Give steps involved in the implementation of iterators in SQLJ. Discuss two types of iterators available in SQLJ.

Ans: The implementation of an iterator in SQLJ basically goes through following four steps:

1. Declaration of the iterator class: In the previous example, the iterator class Bk is declared by using the statement

#sql iterator Bk(String Book title, float Price);

2. Creation and initialization of iterator object: An object of the iterator class is created and initialized with a set of tuples returned as a result of the SQL query statement.

3. Accessing the tuples with the help of an iterator object: An iterator is set to the position just before the first row in the result of the query, which becomes the current tuple for the iterator. The next() function is then applied on the iterator repeatedly to retrieve the subsequent tuples from the result.

4. Closing the iterator object: An object of iterator is closed after all the tuples associated with the result of SQL query are processed.

There are two types of iterator classes, namely named and positional iterators. In case of named iterators both the variable types and the name of each column of the iterator is specified. This helps in retrieving the individual columns by their name. Similar to the previous example, the sample program to retrieve Book_title from the iterator book, the expression book. Book_title () is used. While, in case of positional iterators, only the variable type for each column of iterator is specified. The individual columns of the iterator are accessed using the FETCH . . INTO statement like embedded SQL. Both types of iterators have the same performance and can be used according to the user requirement.

33. Write SQL queries for the following based on the Online Book database:

(a) Retrieve the city, phone and url of the author whose name is Lewis Ian.

(b) Retrieve the name, address and phone of all the publishers located in New York state.

(c) Retrieve the title and price of all textbooks with a page count greater than 600.

(d) Retrieve the ISBN, title and price of the books belonging to either novel or language book category.

(e) Retrieve the title and price of all books published by Hills Publications.

(f) Retrieve the book title, reviewers ID and rating of all textbooks.

(g) Retrieve the ID, name, url of author and category of the book C++.

(h) Retrieve the book title, price, author name and url for the publishers Bright Publications.

Ans:

(a) SELECT City, Phone, URL FROM AUTHOR WHERE Aname = ‘Lewis Ian’;

(b) SELECT Pname, Address, Phone FROM PUBLISHER WHERE State = ‘New York’;

(c) SELECT Book_title, Price FROM BOOK WHERE Category = ‘Textbook’ ANDPage_count>600;

(d) SELECT ISBN, Book_title, Price FROM BOOK WHERE Category = ‘Novel’ OR Category = ‘Language Book’;

(e) SELECT Book_title, Price FROM BOOK AS B, PUBLISHER AS P WHERE B.P_ID=P.P_ID ANDPname=‘Hills Publications’;

(f) SELECT Book_title, R_ID, Rating FROM BOOK AS B, REVIEW AS R WHEREB.ISBN=R.ISBN AND Category = ‘Textbook’;

(g) SELECT AB.A_ID, Aname, URL, Category FROM BOOK AS B, AUTHOR AS A, AUTHOR_BOOKAS AB WHERE A.A_ID=AB.A_ID AND AB.ISBN=B.ISBN AND Book_title= ‘C++’;

(h) SELECT Book_title, Price, Aname, URL FROM BOOK AS B, AUTHOR_ BOOK AS AB, AUTHOR AS A, PUBLISHER AS P WHERE B.ISBN=AB.ISBN AND AB.A_ID=A.A_ID ANDB.P_ID=P.P_ID AND Pname=‘Bright Publications’;

34. Specify the following queries on the Online Book database in SQL:

(a) In the BOOK relation, increase the price of all books belonging to novel category by 10%.

(b) In the PUBLISHER relation, change the phone of Wesley Publications to 9256774.

(c) Calculate and display the average, maximum and minimum price of a book from each category.

(d) Calculate and display the average, maximum and minimum price of a book from each publisher.

(e) Delete details of all the books having a page count less than 100.

(f) Retrieve details of all the books whose name begins with character D.

(g) Retrieve details of all the publishers located in state Georgia.

(h) Retrieve details of all the authors residing in the city New York and whose name begins with character J.

(i) Retrieve the book title, name of publisher and author name of all language books.

(j) Retrieve the book details having a price that equals the average price of all the books.

(k) Retrieve details of authors residing in the same city as Lewis Ian.

Ans:

(a) UPDATE BOOK SET Price=Price + 0.10 * Price WHERE Category=‘Novel’;

(b) UPDATE PUBLISHER SET Phone=‘ 92 5 67 7 4’ WHERE Pname=‘Wesley Publications’;

(c) SELECT AVG(Price), MAX(Price), MIN(Price) FROM BOOK GROUP BY Category;

(d) SELECT AVG(Price) , MAX(Price), MIN(Price) FROM BOOK, PUBLISHER WHEREBOOK.P_ID=PUBLISHER.P_ID GROUP BY Pname;

(e) DELETE FROM BOOK WHERE Page_count<100;

(f) SELECT * FROM BOOK WHERE Book_title LIKE ‘D%’;

(g) SELECT * FROM PUBLISHER WHERE State=‘Georgia’;

(h) SELECT * FROM AUTHOR WHERE City=‘New York’ AND Aname LIKE ‘J%’;

(i) SELECT Book_title, Pname, Aname FROM BOOK, PUBLISHER, AUTHOR_BOOK, AUTHORWHERE BOOK.P_ID=PUBLISHER.P_ID AND Book. ISBN=AUTHOR_BOOK.ISBN ANDAUTHOR_BOOK.A_ID=AUTHOR.A_ID AND Category=‘Language BOOK’;

(j) SELECT * FROM BOOK WHERE Price=(SELECT AVG(Price) FROM BOOK);

(k) SELECT * FROM AUTHOR WHERE City IN (SELECT City FROM AUTHOR WHERE Aname=‘Lewis Ian’);