Day 16(2): SQL Queries and Integrity Constraints

Hello Dear Students, 
   Hope you all are doing good...

Today we are going to cover the topic SQL Queries and Constraints. 

Let's get started....

SQL QUERIES - SQL stands for Structured Query Language. SQL ek query language hai jisme queries user query karta hai and result fetch karta hai. Isme bahut saari queries hoti hain but hum basic queries hi karenge.

CREATE TABLE - RDBMS mein saara data tables ki hi form mein store kiya jata hai. Sabse pehle tables create kiye jate hain and then operations/queries perform hoti hai created tables par. Tables create karne ke liye CREATE TABLE command use ki jati hai. For example,

SQL> CREATE TABLE employee
           (emp_id  NUMBER(4),
            name     VARCHAR2(15),
            dob        DATE,
            post       VARCHAR2(20),
            phone    NUMBER(10)
           );
Table created


INSERT STATEMENT - Insert statement se new row ko add kiya ja sakta hai. INSERT command use hoti hai new row ko table mein add karne ke liye. For example, 

SQL> INSERT INTO EMPLOYEE
           (emp_id, name, dob, post, phone)
           VALUES
           (001, 'Ram', '01-01-1990', 'Manager', 0099092902);
1 row created


UPDATE STATEMENT Jo table already hai agar uske data mein kuch changes karne hai to UPDATE statement use ki jati hai. For example,

SQL> UPDATE EMPLOYEE
           SET PHONE = 0900987009;
* rows updated.


DELETE STATEMENT - Table mein kisi row ko delete karne ke liye DELETE command use ki jati hai. DELETE command basically WHERE clause ke saath kaam karti hai.Lekin us row ko delete nahi kiya ja sakta jo row primary key ho and jo kisi aur table mein foreign key ho. Example of DELETE statement,

SQL> DELETE FROM EMPLOYEE
           WHERE emp_id = 001;
1 row deleted.


TRUNCATE TABLE - TRUNCATE statement table ki all rows ko delete karne ke liye use ki jati hai. Isme table delete nahi hota and columns delete nahi hote hain. Sirf rows delete ho jati hai and table new table jaisa ban jata hai.

SQL> TRUNCATE TABLE EMPLOYEE;
Table truncated.


COMMIT - Database mein jo bhi changes kiye jate hain wo permanent nahi hote jab tak user database ko COMMIT statement pass nh karta. COMMIT statement database ke changes ko permanently save kar deti hai and jab bhi hum exit karte hai to automatically changes save ho jate hain. SET AUTCOMMIT ON se hum automatically changes save karne ke liye database ko command kar dete hain and database automatically every time changes ko save kar deta hai. Syntax-

SQL> COMMIT;


ROLLBACK - ROLLBACK statement sabhi changes ko Undo karne ke liye use ki jati hai jo bhi changes COMMIT statement ke baad hue ho. Syntax-

SQL> ROLLBACK;



INTEGRITY CONSTRAINTS - Integrity constraints hume correct and accurate data provide karane ke liye use kiye jate hain. Integrity constraints agar follow nahi kiye jaate to database rollback ho kar error message show kr deta hai. Integrity constraints are as follows-

  • NOT NULL integrity constraint - Null means the value is either missing, unknown, or inapplicable. By default, jab table create kiya jata hai to uske columns ko Null value assign ki jati hai. NOT NULL constraint ke according table ke columns ki values Not Null honi chahiye. Is constraint ko hum multiple columns par bhi apply kar sakte hain. For example,
CREATE TABLE instructor
(inst_id   NUMBER(4)  CONSTRAINT NN_INSTID NOT NULL,
 name      VARCHAR2(15)  NOT NULL,
 phone     NUMBER(10)   
);

  • PRIMARY KEY constraint - Primary key column hota hai jiski rows ki values unique ho and koi bhi value repeat na ho. PRIMARY KEY constraint basically table ke liye primary key create karti hai. Primary key wale columns ki value Null value nahi ho sakti. Primary key is combination of unique key constraints and NOT NULL constraints. In Oracle8i, maximum number of primary key is 32 in a table. For example,
CREATE TABLE instructor
(inst_id   NUMBER(4)  CONSTRAINT PK_INSTRUCTOR PRIMARY KEY,
 name     VARCHAR 2(15)  CONSTRAINT NN_NAME NOT NULL,
 phone    NUMBER(10)
);

  • FOREIGN KEY constraint - It is also known as Referential integrity constraint. Foreign key is identifier used to represent another table. Foreign key columns or set of columns hote hain jo ki doosre table ki primary key ki tarah act karti hai. Agar 2 tables ek doosre se relate kar rahe hain means data same hai columns ka to wo column table ka foreign key ban jayega jisme doosre table ki primary key ho. Agar table mein primary key nahi hogi to foreign key bhi nahi ho sakti. For example,
SQL> CREATE TABLE instructor
(inst_id  NUMBER(4)   PRIMARY KEY,
 name     VARCHAR2(15),
 dept_id  NUMBER(4)  CONSTRAINT FK_INSTRUCTOR
 REFERENCES department(dept_id)
);

  • UNIQUE KEY constraint - UNIQUE KEY constraint states that value in column is unique and not null. Unique key can enter null values without NOT NULL constraint also. 
SQL> CREATE TABLE instructor
(inst_id  NUMBER(4)  PRIMARY KEY,
 name    VARCHAR2(15),
 email    VARCHAR2(30)  CONSTRAINT UN_INSTRUCTOR UNIQUE
);

  • CHECK constraint - It is also called Business rule constraint. CHECK constraint basically check karne ke liye hota hai ki value correct hai ya incorrect or acceptable hai ya nahi. It is Boolean condition that is true or false. If value is true, then the data is accepted and stored otherwise if false then it shows an error message.
SQL> CREATE TABLE instructor
(inst_id  NUMBER(4)  PRIMARY KEY,
 name     VARCHAR2(15),
 post       VARCHAR2(20) CONSTRAINT CK_INSTRUCTOR CHECK(post 
 IN('LECTURER','PROFESSOR','READER')),
);


More about keys in detail- Click here for keys topic



Best of Luck Students,
 Do share, subscribe and comments if you like our efforts.