Advance database query

 

Advance QUERY  Concept

[1]

Top Only

SELECT TOP 3 * FROM stu;

 

[2]

 

Top Operator

SELECT TOP 3 WITH TIES name

FROM stu

ORDER BY name  ASC;

 

[3]

NOT operator

SELECT id,NAME

FROM stu

WHERE NOT id=2;

 

 

[4]

Combining Multiple Operators

SELECT *

FROM Customers

WHERE (country = 'USA' OR country = 'UK') AND age < 26;

 

SELECT *

FROM customers

WHERE NOT country = 'USA' AND NOT last_name = 'Doe';

 

 

5

DISTINCT

 

SELECT DISTINCT country

FROM Customers;

 

6

DISTINCT With COUNT

SELECT COUNT(DISTINCT country)

FROM Customers;

 

7

Select as operation

 

SELECT id AS student_id , name AS student_name

FROM stu;

 

8

Concat to fields in database

 

SELECT CONCAT(id, ' ', name) AS full_name

FROM stu;

 

 

9

Using count with AS

SELECT COUNT(*) AS total_student

FROM stu;

 

 

10

Not in operator

SELECT id, name

FROM stu

WHERE name NOT IN ('Atharva Thaker', 'Chetna Deshmukh');

 

11

Null and not null

 

SELECT *

FROM stu

WHERE email IS NULL;

 

 

SELECT *

FROM stu

WHERE email IS NOT NULL;

 

12

Like operator

 

SELECT *

FROM stu

WHERE name LIKE 'Aman';

 

SELECT *

FROM stu

WHERE name LIKE 'Am%';

 

SELECT *

FROM stu

WHERE name LIKE '%ik';

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL ANY and ALL

 

SQL ANY Operator

SQL ANY compares a value of the first table with all values of the second table and returns the row if there is a match with any value.