SQL Constaints.sql

CREATE DATABASE CONSTARINTS;
USE CONSTARINTS;

CREATE TABLE CONST(
ID INT UNIQUE  PRIMARY KEY,
name VARCHAR(50) NOT NULL
);

INSERT INTO CONST VALUES(1, "HELLO");
INSERT INTO CONST VALUES(2, "WORLD");
INSERT INTO CONST VALUES(3, "hELLO");
SELECT * FROM CONST;

CREATE TABLE EMPLOYE(
id INT,
Salary INT DEFAULT 50000
);

INSERT INTO EMPLOYE(id) VALUES(250);
SELECT * FROM EMPLOYE;

CREATE TABLE CITY(
id INT PRIMARY KEY,
City VARCHAR(50) CHECK (city="Peshawar"),
age INT CHECK (age >=18)
);

INSERT INTO CITY VALUES (1, "Peshawar", 20);
INSERT INTO CITY VALUES (2, "Peshawar", 25);
SELECT * FROM CITY;

CREATE TABLE Info_Students(
Marks INT NOT NULL,
City VARCHAR(50)
);

INSERT INTO Info_Students VALUES(56, "PESHAWAR");
INSERT INTO Info_Students VALUES(89, "PESHAWAR");
INSERT INTO Info_Students VALUES(90, "kARACHI");
INSERT INTO Info_Students VALUES(69, "KARACHI");
INSERT INTO Info_Students VALUES(70, "LAHORE");
INSERT INTO Info_Students VALUES(80, "ISALMABAD");

-- WE CAN USE AND , OR , NOT
SELECT * FROM Info_Students WHERE Marks>=70 AND City= "PESHAWAR";
SELECT * FROM Info_Students WHERE Marks>=70 OR City= "PESHAWAR";
SELECT * FROM Info_Students WHERE Marks BETWEEN 80 AND 90;
SELECT * FROM Info_Students WHERE City IN ("PESHAWAR", "LAHORE");
SELECT * FROM Info_Students WHERE City NOT IN ("PESHAWAR", "LAHORE");

SELECT * FROM Info_Students LIMIT 3; 
SELECT * FROM Info_Students ORDER BY Marks ASC;
SELECT * FROM Info_Students ORDER BY City DESC;
SELECT * FROM Info_Students ORDER BY City DESC LIMIT 3;