Execute basic SQL statements using the following a) Projection b) Selection c) arithmetic operators d) Column aliases e) Concatenation operator f) Character Strings g) Eliminating Duplicate Rows h) Limiting Rows Using • Comparison operators • LIKE,BETWEEN AND,IN operators • Logical Operators i) ORDER BY Clause • Sorting in Ascending Order • Sorting in Descending Order • Sorting by Column Alias • Sorting by Multiple Columns

 

EXERCISE : 3

AIM : Execute basic SQL statements using the following                                                     

a) Projection                                        b) Selection
c) arithmetic operators                        d) Column aliases
e) Concatenation operator                   f) Character Strings
g) Eliminating Duplicate Rows                                  

h) Limiting Rows Using
• Comparison operators                      • LIKE,BETWEEN AND,IN operators
• Logical Operators                                                                                               

i) ORDER BY Clause
• Sorting in Ascending Order             • Sorting in Descending Order
• Sorting by Column Alias                 • Sorting by Multiple Columns

Description :

a)      Projection :

                    It produces a new relation with only some of the attributes of R , and removes duplicate tuples.

Example :

Find the names and ages of all the sailors.

 

b)     Selection :

                     It selects all tuples that satisfy the selection conditions from a relation R.

              Example :

            Find all sailors with a rating above 7.

 

c)      Arithmetic operators :

                      These are used to perform mathematical calculations like addition, subtraction, multiplication, division and modulus in SQL .

Arithametic operators	Example / Description
+      (addition)	A+B
-          (subtraction)	A-B
*      (multiplication)	A*B
/     (Division)	A/B
% (Modulus)	A%B

Example:

 

 

 

 

 

 

 

 

  1. Add a rating 5 to all sailors and display sname, rating, rating+5 from a sailors relation.

2. Reduce the age of all sailors by 3 and display sid, name old age and new age of all the sailors .

3. Multiply the rating of all the sailors by 5 and display sid, old rating and new rating from sailors.

4. Divide bid with sid and display resultant sid, bid/sid for reserves.

5. Retrieve age, age%2 using Mod operator on sailors.

d)     Colmn aliases :

                          An alias is created with the AS keyword . it gives  a table , or a column in a table a temporary name.

     Example :

1.      Apply the column aliasing to sailors table by aliasing rating as star rating.

e)      Concatenation operator :

                                 It is used to link columns or character strings .

          Example :

1.      Apply concatenation operator on dual or any relation

f)       Character Strings :

                                 A character string is any sequence of zero or more alphanumeric characters that belong to a given character set .

       Example :

1.      Apply Char functions on dual

g)      Eliminating Duplicate Rows :

                                     Deleting the duplicate rows or tuples or records from the table .

Syntax :

Select * from table_name where rowid in(select max(rowid) from table_name group by attribute);

Example :

Select * from duplicate where rowid in(select max(rowid) from duplicate group by id);

h)     Limiting rows using

Comparison operator :

                          Comparison operators are used to compare according to the conditions.

Operator	Symbol
Less than	<
Gretar than	>
Equal to	=
Less than or equal to	<=
Greather than or equal to	>=
Not equal	<>

 

Example :

         1.Find sid,ages of sailors whose age is above 45

         2.Find sid,sname of sailors whose rating is below 5

         3.Find sid,sname of sailors whose rating is greater than or equal to 7

         4.Find sid,sname of sailors whose age is less than or equal to 32

         5.Find sid,sname of sailors whose rating is equal to 7

         6.Find sid,sname of sailors whose rating is not equal to 7.

i) LIKE , BETWEEN , AND , IN operators :

 LIKE :

Like is used to take the check whether the letter or a string starts with a particular name or character.

It is usually calculated using percentile ( % ) for n number of characters

underscore ( _ ) for the single characters .

 Example :

1.    Find the ages of sailors whose name begins b and ends with b and has at least three characters.

2.    Find the sid’s, names of sailors whose name begins with a and has at least three characters.

Not Like : It just works opposite to the LIKE operator.

Example :

1.    Find the names of sailors whose name doesn’t begins with a and has at least three characters.

BETWEEN :

It is used to check the condition in particular range or not.

Example :

1.    Find the details of sailors age between 40 and 60.

AND :

And is used to compare between to conditions or it is used to combine.

Example :

1.        Find the names of sailors who have reserved a red color boat.

IN :

Example :

1.    Find the snames of sailors who have reserved boat 103 (Using IN)

NOT IN :

  Example :

1.         Find the snames of sailors who have not reserved boat 103 (using NOT IN)

Logical Operators :

1.      ALL :

                    It returns true if all of the subquery values meet the condition.

Syntax :

SELECT column/attribute

FROM table_name

WHERE condition_attribute = ALL (SELECT condition_attribute FROM table_name2 WHERE condition);

Example :

Select sname from sailors where sid = all(select sid from reserves where bid = 103);

2.      AND :

                     It rerurns true if all the conditions separated by and is true.

Syntax :

SELECT * FROM table_name

WHERE attribute = value  AND attribute = value;

Example :

Select * from sailors where sname = ‘lubber’ and sid = 31;

3.      ANY :

     It returns true if any of the subquery values meet the condition.

Syntax :

SELECT * FROM table_name

WHERE condition ANY (SELECT attribute FROM table_name2 WHERE condition);

Example :

Find the sailors whose rating is better than soe sailor called Horatio

 

4.      EXIST :

      It returns true if the subquery returns one or more records.

Syntax :

SELECT column

FROM table_name

WHERE EXIST (SELECT attribute FROM table_name2 WHERE condition AND condition);

Example :

Find the names of sailors who have reserved boat number 103

 

5.      NOT :

    Displays a record if the condition is not true.

Syntax :

SELECT * FROM table_name

WHERE attribute NOT LIKE 'string';

Example :

Select * from sailors where sname not like ‘a%’;

 

6.      OR :

    It returns true if any of the conditions separated by or is true.

Syntax :

SELECT * FROM table_name

WHERE attribute = value OR attribute = value;

Example :

Select * from sailors where sname = ‘lubber’ or sid = 103;

     I ) ORDER by clause :

ORDER BY

The ORDER BY statement in sql is used to sort the fetched data in either ascending or descending according to one or more columns.

• By default ORDER BY sorts the data in ascending order.
• We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Syntax of all ways of using ORDER BY is shown below:

• Sort according to one column: To sort in ascending or descending order we can use the keywords ASC or DESC respectively.
  Syntax:
  SELECT * FROM table_name ORDER BY column_name ASC|DESC;
• Sort according to multiple columns: To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by (,) operator.
  Syntax:
• SELECT * FROM table_name ORDER BY column1 ASC|DESC , column2 ASC|DESC;

Sorting in ascending order :

Example :

1.    Find the details of sailors, ordered by sid

Sorting in descending order :

Example :

1.              Find rating and sid’s of sailors ordered by rating in descending order

Sorting by column alias :

   Example :

Sorting by multiple columns :

 Example :

1.    Find the details of all sailors ordered by sname and sid .