12 TWELVE DAY.sql

-- CONSTRAINTS : THE rules that will apply to limit the data in my table.
-- CONTAINS is used to check whether a column contains specific text, but its meaning depends on the database system

SHOW DATABASES;
USE REGEX;
CREATE TABLE TEST1(SNO INT);
INSERT INTO TEST1 VALUES(10);
INSERT INTO TEST1(SNO) VALUES(20);
INSERT INTO TEST1(SNO) VALUES(NULL),(30);
SELECT * FROM TEST1;
SELECT COUNT(SNO),COUNT(*) FROM TEST1;

-- 1. NOT NULL CONSTRAINT
CREATE TABLE TEST2(SNO INT NOT NULL , SALARY INT);
INSERT INTO TEST2(SNO,SALARY) VALUES(20,1000);
INSERT INTO TEST2(SNO,SALARY) VALUES(20,NULL);
INSERT INTO TEST2(SNO,SALARY) VALUES(NULL,1000); -- ERROR
INSERT INTO TEST2(SALARY) VALUES(500); -- ERROR

-- 2. DEFAULT CONSTRAINT
CREATE TABLE TEST3(SNO INT NOT NULL default 80, SALARY INT);
INSERT INTO TEST3(SNO,SALARY) VALUES(20,1000);
INSERT INTO TEST3(SALARY) VALUES(500); 
SELECT * FROM TEST3;

-- 3. UNIQUE CONSTRAINT
CREATE TABLE TEST4(SNO INT NOT NULL, SALARY INT UNIQUE default 100);
INSERT INTO TEST4(SNO,SALARY) VALUES(20,1000);
INSERT INTO TEST4(SNO,SALARY) VALUES(50,1000); -- ERROR DUPLICAT VALUES
INSERT INTO TEST4(SNO) VALUES(500);
INSERT INTO TEST4(SNO) VALUES(500); -- GAVE ERROR 100 ALREADY EXIST
INSERT INTO TEST4(SNO,SALARY) VALUES (1500,NULL);
INSERT INTO TEST4(SNO,SALARY) VALUES (1000,NULL);
SELECT * FROM TEST4;

-- 4. CHECK CONSTRAINTS
CREATE TABLE TEST5(SNO INT, SALARY INT,
CHECK(SNO BETWEEN 1 AND 100),
CHECK(SALARY IN (1000,2000) ));
INSERT INTO TEST5(SNO,SALARY) VALUES(20,1000);
INSERT INTO TEST5(SNO,SALARY) VALUES(150,1000); -- ERROR
INSERT INTO TEST5(SNO,SALARY) VALUES(20,1500); --  ERROR
INSERT INTO TEST5(SNO,SALARY) VALUES(120,1500); -- ERROR

-- USING NAME TO CONSTRAIN TO IDENTIFY THE ERROR
CREATE TABLE TEST6(SNO INT, SALARY INT,
constraint REGEX_TEST6_SNO_CHECK CHECK(SNO BETWEEN 1 AND 100),
constraint REGEX_TEST6_SALARY_CHECK CHECK(SALARY IN (1000,2000) ));
INSERT INTO TEST6(SNO,SALARY) VALUES(20,1000);
INSERT INTO TEST6(SNO,SALARY) VALUES(150,1000); -- ERROR
INSERT INTO TEST6(SNO,SALARY) VALUES(20,1500); --  ERROR
INSERT INTO TEST6(SNO,SALARY) VALUES(120,1500); -- ERROR

-- 5. PRIMARY KEY CONSTRAINTS
CREATE TABLE TEST8(SNO INT PRIMARY KEY, SALARY INT);
INSERT INTO TEST8(SNO,SALARY) VALUES(20,1000);
INSERT INTO TEST8(SALARY) VALUES(500); 
INSERT INTO TEST8(SNO,SALARY) VALUES(NULL,1000);

-- 6.FOREIGN KEY
CREATE TABLE CUSTOMER1(CID INT PRIMARY KEY,CNAME VARCHAR(20));
INSERT INTO CUSTOMER1 VALUES(10,'AMAN'),(11,'ABHI');
SELECT * FROM CUSTOMER1;

CREATE TABLE ORDER1(ORDER_NO INT PRIMARY KEY,CITY VARCHAR(20),CID INT,foreign key (CID) references CUSTOMER1(CID));
INSERT INTO ORDER1 VALUES(1007,'JAIPUR',10),(1008,'GOA',11),(1009,'MUMBAI',10);
INSERT INTO ORDER1 VALUES(10010,'J&K',25); -- ERROR BEACUES 25 IS NOT A CID IN CUSTOMER TABLE
SELECT * FROM ORDER1;