DISTINCT Operator
6. Retrieve all distinct Branch Numbers for all the courses.
-> SELECT DISTINCT BNo
FROM Course;
DISTINCT eliminates duplicate entries in the field. All the values will appear only once.
LIKE Operator
7. Display students’ names and addresses who reside in Delhi.
-> SELECT SName , Address
FROM Student
WHERE Address LIKE ‘%Delhi%’;
LIKE comparison is used for pattern-matching. Partial strings are specified using two reserved characters “%” - replaces zero or more characters, “_” (underscore) replaces one character.
SName | Address | Anjali | 12,Lajpat Nagar, Delhi | Sonai | 19, Patel Road, Delhi |
8. Find all students’ name and birthday who were born in the month of September. (DD-MM-YYYY)
-> SELECT SName, Bdate
FROM Student
WHERE Bdate LIKE _ _ _ 09 _ _ _ _ _;
If the person is born in September, 0 and 9 must be the 4th and the 5th character (MM), respectively, according to our date format. Each underscore holds one character.
SName | Bdate | Varun | 05-09-1990 | Tanvi | 11-09-1991 |
INEQUALITY Operator
9. Retrieve names of students with course names and corresponding resources who have more than 6 resources.
-> SELECT SName , CName, No.of_Resources
FROM Student AS S, Resource AS R, Course AS C
WHERE S.St_Id = R.St_Id AND R.CId = C.CId AND No_of_Resources >6;
There are 2 join conditions to join Student-Resource tables and Course-Resource tables. The “>” inequality compares the resources and displays the result satisfying the condition ( resources > 6). Aliasing is done as CId and St_Id are present in two tables .
SName | CName | No_of_ resources | Namita | Chemical Analysis | 8 | Tanvi | Organic Chemistry | 7 |
BETWEEN Operator
10. Retrieve the Student Name who have resources between 3 to 5 in any course.
-> SELECT SName, No_of_Resources
FROM Student AS S, Resource AS R
WHERE (No_of_Resources BETWEEN 4 AND 6) AND S.St_Id = R.St_Id;
This means No.of Resources (>= 4 and <=6). Join condition is used to join the 2 tables.
| SName | No_of_
Resources | Anjali | 4 | Sonia | 5 | Mudit | 3 | Namita | 3 | Parag | 4 | Varun | 4 |
UNION Operator
11. Display all course names studied by ‘Ahmad’ or which have less than 3 resources to one or more students.
->( SELECT CName
FROM Course AS C, Student AS S , Resource AS R
WHERE C.CId = R.CId AND R.St_Id = S.St_Id AND SName = ‘Ahmad’ )
UNION
( SELECT DISTINCT CName
FROM Course AS C, Resource AS R,
WHERE C.CId = R. CId AND No_of_Resources < 3 );
The first select retrieves courses studied by ‘Ahmad’ using 2 join conditionsm and the second retrieves courses which have less than 4 resources using simple join conditions. Note that UNION operation can be applied only if the two relations have the same attribute in both the relations.
CName | Building Engg | Data Structures | Circuit Design | Electrical Science |
NULL Operator
12. Retrieve the student names who have no resources allocated for atleast one course.
-> SELECT SName
FROM Student AS S, Resource AS R
WHERE S.St_Id = R.St_Id AND No_of_Resources IS NULL;
If the no of resources for a given course is NULL, it displays the student names using join condition of Student-Resource table.
( if it was 1211 - 61- NULL), then
JOIN Operator 13. Retrieve the courses in the ‘Electronics’ branch.-> SELECT CName FROM (Course AS C JOIN Branch AS B ON C.BNo = B.BNo) WHERE BName=’Electronics’;JOIN Operator joins 2 tables using the condition after ON. There is no need to write the join condition in the WHERE clause. The 2 tables joined here are Course and Branch.CName | Circuit Design | Electrical Science |
TOP Operator14. Retrieve the first two records of the Student. -> SELECT TOP 2 * FROM Student;| SName | St_Id | Bdate | Address | Sex | Br_No | | Anjali | 1201 | 26-06-1991 | 12,Lajpat Nagar,Delhi | F | 5 | | Ahmad | 1202 | 20-10-1990 | 2113,Sec42,Chandigarh | M | 5 |
|
|
