SQL Notes
- Data Definition Language(DDL): It changes a table’s structure by adding, deleting, and altering its contents.
- CREATE: Used to create a new table in the database.
Example:
CREATE TABLE STUDENT
(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);
- ALTER: Used to modify the structure of a table.
- Used to alter contents of a table by adding some new column or attribute, or changing some existing attribute.
Example:
ALTER TABLE STUDENT ADD(ADDRESS VARCHAR2(20));
ALTER TABLE STUDENT MODIFY (ADDRESS VARCHAR2(20));
- DROP: Used to delete the structure and record stored in the table.
Example:
DROP TABLE STUDENT;
- TRUNCATE: Used to delete all the rows from the table, and free up the space in the table.
Example:
TRUNCATE TABLE STUDENT;
2. Data Manipulation Language(DML): It is used for modifying a database, and is responsible for any form of change in a database.
INSERT: Used to insert data in the row of a table.
Example:
INSERT INTO STUDENT (Name, Subject) VALUES (“Scaler”, “DSA”);
- UPDATE: Used to modify the existing records in a table
- Used to update the value of a table’s column.
Example:
UPDATE STUDENT
SET User_Name = ‘Interviewbit’
WHERE Student_Id = ‘2’
- DELETE: Used to delete one or more rows in a table.
Example:
DELETE FROM STUDENT
WHERE Name = “Scaler”;
3. Data Control Language(DCL): These commands are used to grant and take back access/authority (revoke) from any database user. Some commands that are a part of DCL are:
- Grant: Used to grant a user access privileges to a database.
Example:
GRANT SELECT, UPDATE ON TABLE_1 TO USER_1, USER_2;
- Revoke: Used to revoke the permissions from an user.
Example:
REVOKE SELECT, UPDATE ON TABLE_1 FROM USER_1, USER_2;
4. Transaction Control Language: These commands can be used only with DML commands in conjunction and belong to the category of auto-committed commands. Some commands that are a part of TCL are:
- COMMIT: Saves all the transactions made on a database.
Example:
DELETE FROM STUDENTS
WHERE AGE = 16;
COMMIT;
In the above database, we delete the row where AGE of the students is 16, and then save this change to the database using COMMIT.
- ROLLBACK: It is used to undo transactions which are not yet been saved.
Example:
DELETE FROM STUDENTS
WHERE AGE = 16;
ROLLBACK;
By using ROLLBACK in the above example, we can undo the deletion we performed in the previous line of code, because the changes are not committed yet.
- SAVEPOINT: Used to roll transactions back to a certain point without having to roll back the entirety of the transaction.
Example:
SAVEPOINT SAVED;
DELETE FROM STUDENTS
WHERE AGE = 16;
ROLLBACK TO SAVED;
5. Data Query Language: It is used to fetch some data from a database. The command belonging to this category is:
- SELECT: It is used to retrieve selected data based on some conditions which are described using the WHERE clause. It is to be noted that the WHERE clause is also optional to be used here and can be used depending on the user’s needs.
Example: With WHERE clause,
SELECT Name
FROM Student
WHERE age >= 18;
Example: Without WHERE clause,
SELECT Name
FROM Student
In the first example, we will only select those names in the Student table, whose corresponding age is greater than 17. In the 2nd example, we will select all the names from the Student table.
5. SQL Constraints
Constraints are rules which are applied on a table. For example, specifying valid limits or ranges on data in the table etc.
The valid constraints in SQL are:
1. NOT NULL: Specifies that this column cannot store a NULL value.
Example:
CREATE TABLE Student
(
ID int(8) NOT NULL,
NAME varchar(30) NOT NULL,
ADDRESS varchar(50)
);
2. UNIQUE: Specifies that this column can have only Unique values, i.e the values cannot be repeated in the column.
Example:
CREATE TABLE Student
(
ID int(8) UNIQUE,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);
3. Primary Key: It is a field using which it is possible to uniquely identify each row in a table. We will get to know about this in detail in the upcoming section.
4. Foreign Key: It is a field using which it is possible to uniquely identify each row in some other table. We will get to know about this in detail in the upcoming section.
5. CHECK: It validates if all values in a column satisfy some particular condition or not.
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10),
AGE int CHECK (AGE < 20)
);
Here, in the above query, we add the CHECK constraint into the table. By adding the constraint, we can only insert entries that satisfy the condition AGE < 20 into the table.
6. DEFAULT: It specifies a default value for a column when no value is specified for that field.
Example:
CREATE TABLE Student
(
ID int(8) NOT NULL,
NAME varchar(50) NOT NULL,
CLASS int DEFAULT 2
);
In the above query, we set a default value of 2 for the CLASS attribute. While inserting records into the table, if the column has no value specified, then 2 is assigned to that column as the default value.
6. Crud Operations in SQL
CRUD is an abbreviation for Create, Read, Update and Delete. These 4 operations comprise the most basic database operations. The relevant commands for these 4 operations in SQL are:
- Create: INSERT
- Read: SELECT
- Update: UPDATE
- Delete: DELETE
- INSERT: To insert any new data ( create operation — C ) into a database, we use the INSERT INTO statement.
SQL Syntax:
INSERT INTO name_of_table(column1, column2, ….)
VALUES(value1, value2, ….)
- SELECT: We use the select statement to perform the Read ( R ) operation of CRUD.
SQL Syntax:
SELECT column1,column2,.. FROM name_of_table;
- DELETE:
The Delete command is used to delete or remove some rows from a table. It is the ‘D’ component of CRUD.
SQL Syntax:
DELETE FROM name_of_table
WHERE condition1, condition2, …;
Joins -
SQl Wildcards-
=, like, %
Alter cmd-
Alter Table table_name
ADD/ ALTER /DELETE
Drop and Drop Cascade-
Drop delete only selected table.
Drop Cascade deletes table as well as all other elements related to that table
