Comp12 cont…

Using Quotes

• Note that we have used single quotes around the conditional values in the examples.

• SQL uses single quotes around text values (most database systems will also accept double quotes). Numeric values should not be enclosed in quotes.

For text values:

This is correct:

SELECT * FROM Persons WHERE FirstName =

'Tove'

This is wrong:

SELECT * FROM Persons WHERE FirstName =

Tove

For numeric values:

This is correct:

SELECT * FROM Persons WHERE Year > 1965

This is wrong:

SELECT * FROM Persons WHERE Year > '1965'

IN operator:

• IN operator in the where clause can be used to check for discrete values.

Example 1: Write a query to get those names of all students whose marks are

90 or 92.• Ans: SELECT name FROM student WHERE

marks IN (90, 92);

Example 2: Write a query to get details of all students whose belong to

class C12 or E12.

• Ans: SELECT * FROM student WHERE class IN (‘C12’, ‘E12’);

• BETWEEN-AND Operator: This operator is used to define a range of values. The lower and higher value is also included in the range of values.

Example: Write a query to display the roll & name of all students whose

marks is between 90 & 95.

• Ans: SELECT roll, name FROM student WHERE marks BETWEEN 90 AND 95;

LIKE operator:

• This operator is used to check character type fields. It is used to check substrings. There are two types of wild cards used with LIKE operator.

• Underscore (_): It stands for a single character. e.g.: h_t can stand for hat, hit, hot etc.

• Percentage (%): It stands for occurrence of 0 or more characters.

Example 1: Select name, roll from student where name like ‘Sh%’;

Example 2: Write a query to display the details of all students whose

names start with ‘R’;

• Ans: SELECT * FROM student WHERE name LIKE ‘R%’;

LOGICAL or BOOLEAN operators:

• The logical operators AND, OR, NOT are used to have two or more criteria in a single command.

Example 1: Write a query to display

the name and roll of all students who are in class C12 and their age is 15.

• Ans: SELECT name, roll FROM student WHERE class = ’C12’ AND age = 5;

Example 2: Write a query to display the details of all students who are in class E12 or whose age is above 13.

• Ans: SELECT * FROM student WHERE class = ‘E12’ OR age >13;

Aggregation function:

• AVG - Get the average of a specified column. • COUNT - Get the quantity of values in the

column. • MAX - Return the maximum value in a

specified column. • MIN - Return the minimum value in a specified

column. • SUM - Return the sum of all numeric values in

the specified column.

Example:

• SELECT MAX(population)FROM citylist;WHERE state = 'Indiana';

1. Write the correct SQL commands for the following on the basis of table

Supplier.

No. Name Price Supplier Stock

1 Motherboard 7000 Intel 20

2 Keyboard 1000 TVSE 70

3 Mouse 500 Logitech 60

4 Soundcard 600 Samsung 50

5 Speaker 600 Samsung 25

6 Monitor 3000 Philips 22

7 CD-ROM 2800 Creative 32

8 Printer 7900 HP 10

1. List all Name and Price with price between 3000 and 7000.

2. To set the price to 1200 for ‘Keyboard’.3. To delete rows with stock between 20 and 40.4. To display Name, Price, stock of all suppliers

whose name starts with 'S'.5. To display supplier without duplication.6. To insert a row with appropriate values.7. To increase the stock of all Samsung suppliers.8. To display all supplier details whose supplier is

Philips and stock is above 40.9. To display name, supplier, stock for name as

keyboard or mouse.

1. SELECT Name, Price FROM Supplier WHERE Price BETWEEN 3000 AND 7000;

2. UPDATE Supplier SET Price=Price + 1200 WHERE Name = ‘Keyboard’;

3. DELETE FROM Supplier WHERE Stock BETWEEN 20 AND 40;

4. SELECT Name, Price, Stock FROM Supplier WHERE Supplier LIKE ‘S%’;

5. SELECT DISTINT Supplier FROM Supplier;6. INSERT INTO Supplier VALUES(9,’Headphone’, 50,

‘Logitech’, 10);7. UPDATE Supplier SET Stock=Stock + 10;8. SELECT * FROM Supplier WHERE Supplier=’Philips’ AND

Stock > 40;9. SELECT Name, Supplier, Stock FROM Supplier WHERE

IN(‘Keyboard’,’Mouse’);