Database

 -----------------------------------------------------------------------------------------------------------------

Highest 3rd Price

(SQL SERVER):  TOP

select min(price) from (SELECT TOP 3 * FROM Products order by price desc);

(MySQL): LIMIT

select * from Products ORDER BY `price` desc limit 5,1;

(Oracle): FETCH FIRST or ROWNUM

SELECT * FROM Products WHERE ROWNUM <= 3;

SELECT min(price) FROM Products ORDER BY `price` FETCH FIRST 3 ROWS ONLY;

--------------------------------------------------------------------------------------------------------------

Select students has more than 500 marks

select STUDENT_ID, sum(STUDENT_MARKS) as SUM_OF_MARKS 

from marks group by STUDENT_ID 

having sum(STUDENT_MARKS) >= 500 

order by sum(STUDENT_MARKS) DESC;

-----------------------------------------------------------------------------------------------------------

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly