EXPERIMENT No: 6
Aim:
1. Simple queries: selection, projection, sorting on a simple table for employee database
2. Small-large number of attributes
3. Distinct output values Renaming attributes
4. Computed attributes
5. Simple-complex conditions (AND, OR, NOT)
6. Partial Matching operators (LIKE, %, _, *, ?)
7. ASC-DESC ordering combinations
8. Checking for Nulls.
Description:
DISTINCT OUTPUT VALUES: The SELECT DISTINCT statement is used to return only
distinct (different) values.
Syntax:
SELECT DISTINCT column1, column2, ...FROM table_name;
Example 1: SELECT DISTINCT City FROM student;
Example 2: SELECT COUNT(DISTINCT City) FROM student;
RENAMING ATTRIBUTES: Sometimes we may want to rename our table to give it a
more relevant name. For this purpose, we can use ALTER TABLE to rename the name of
table.
Syntax:
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
Example 1: ALTER TABLE Student RENAME COLUMN NAME TO FIRST_NAME;
COMPUTED ATTRIBUTES: The column of whose values depend on some existing
column is called as computed attributes/column.
Example: ALTER TABLE STUDENT ADD Fullname varchar(30) as (concat(First_Name, '
', Last_Name));
SIMPLE-COMPLEX CONDITIONS (AND, OR, NOT)
AND Operator: Logical AND operator compares two expressions and returns TRUE if both
the expressions are TRUE.
Syntax: condition1 (AND/OR/NOT) condition2
Example: Complete table
After applying conditions:
SELECT * FROM student WHERE age >= 18 AND age <= 25;
OR Operator: OR operator compares two expressions and returns TRUE if either of the
expressions is TRUE.
Example:
SELECT * FROM student WHERE Address='Jaipur' OR Address='Delhi';
NOT Operator: NOT operator reverses the input.
Example:
SELECT * FROM student WHERE NOT Address='Jaipur';
PARTIAL MATCHING OPERATORS (LIKE, %, _)
LIKE is used for simple pattern matching, % Matches any number of characters, _ matches
exactly one character.
Complete Relation:
Example:
#Starting with the word Rajput
SELECT * FROM student WHERE Full_Name LIKE 'Rajput%';
#Ending with the word Rajput
SELECT * FROM student WHERE Full_Name LIKE '%Rajput';
#The word should be there, anywhere it is.
SELECT * FROM student WHERE Full_Name LIKE '%Rajput%';
#Begning starts with A and ends with A
SELECT * FROM student WHERE Full_Name LIKE 'A%A';
#It will collect all the marks which are in 90 SELECT * FROM student Where Marks LIKE
'9_';
ASC-DESC ORDERING COMBINATIONS
There is way where you can use both orders ASC | DESC, to sort the records based on two or
more column, As sometime we need to show data in order of first column and then by second
column.
Example:
SELECT Year, Marks, Department FROM student ORDER BY Year DESC, Marks ASC,
Department ASC;
CHECKING FOR NULLS
When a column (field) of table has null values then comparison operators do not work on
those columns, in such case take use of IS NULL & IS NOT NULL operators for the null
check.
IS NULL Syntax:
SELECT column_name1, column_name2, column_name3, ...
FROM table_name
WHERE column_nameN IS NULL;
IS NOT NULL Syntax:
SELECT column_name1, column_name2, column_name3, ...
FROM table_name
WHERE column_nameN IS NOT NULL;
Example:
Employee Table
SELECT Employee_Name, Employee_Address,Employee_PhoneNo FROM employee1
WHERE Employee_PhoneNo IS NULL;
SELECT Employee_Name, Employee_Address,Employee_PhoneNo FROM employee1
WHERE Employee_PhoneNo IS NOT NULL;
