Structured Query Language (SQL) is a programming language for storing and processing information in a relational database. A relational database stores information in tabular form, with rows and columns representing different data attributes and the various relationships between the data values. You can use SQL statements to store, update, remove, search, and retrieve information from the database. You can also use SQL to maintain and optimize database performance. SQL (Structured Query Language) is used to perform operations on the records stored in the database, such as Inserting Records, Updating Records, Deleting Records, Creating and Modifying Database Tables, Views.
SQL was invented in the 1970s based on the relational data model. It was initially known as the structured English query language (SEQUEL). The term was later shortened to SQL. Oracle, formerly known as Relational Software, became the first vendor to offer a commercial SQL relational database management system.
1. No programming needed
2. High-Speed Query Processing
3. Standardized Language
4. Portability
5. Interactive language
6. More than one Data View
1. Cost
2. Interface is Complex
3. Partial Database control
Structured Query Language (SQL) commands are specific keywords or SQL statements that developers use to manipulate the data stored in a relational database. You can categorize SQL commands as follows.
Data Definition Language (DDL) : Data Definition Language (DDL) refers to SQL commands that design the database structure. Database engineers use DDL to create and modify database objects based on the business requirements. For example, the database engineer uses the CREATE command to create database objects such as tables, views, and indexes.
Data Query Language (DQL) : Data Query Language (DQL) consists of instructions for retrieving data stored in relational databases. Software applications use the SELECT command to filter and return specific results from a SQL table.
Data Manipulation Language (DML) : Data Manipulation Language (DML) statements write new information or modify existing records in a relational database. For example, an application uses the INSERT command to store a new record in the database.
Data Control Language (DCL) : Database Administrators use Data Control Language (DCL) to manage or authorize database access for other users. For example, they can use the GRANT command to permit certain applications to manipulate one or more tables.
Transaction Control Language (TCL) : The relational engine uses Transaction Control Language (TCL) to automatically make database changes. For example, the database uses the ROLLBACK command to undo an erroneous transaction.
SQL standards are a set of formally defined guidelines of the structured query language (SQL). The American National Standards Institute (ANSI) and International Organization for Standardization (ISO) adopted the SQL standards in 1986. Software vendors use the ANSI SQL standards to build SQL database software for developers.
SQL injection is a cyberattack that involves tricking the database with SQL queries. Hackers use SQL injection to retrieve, modify, or corrupt data in a SQL database. For example, they might fill in a SQL query instead of a person's name in a submission form to carry out a SQL injection attack.
The CREATE DATABASE statement is used to create a new SQL database.
Syntax : CREATE DATABASE Database_Name;
Example : CREATE DATABASE JST_DB;
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax : DROP DATABASE Database_Name;
Example : DROP DATABASE JST_DB;
The RENAME DATABASE statement is used to rename an existing SQL database.
Syntax : RENAME DATABASE old_db_name TO new_db_name;
Example : RENAME DATABASE JST_DB TO JST_DAB;
Syntax : USE database_name;
Example : USE JST_DB;
The CREATE TABLE statement is used to create a new table in a database.
Syntax : CREATE TABLE table_name(column1 datatype, column2 datatype, column3 datatype, .... );
Example : CREATE TABLE STUDENT (STUD_ID int, FULL_NAME varchar(255), AGE int, ADDRESS varchar(255), CITY varchar(255));
The DROP TABLE statement is used to drop an existing table in a database.
Syntax : DROP TABLE table_name;
Example : DROP TABLE Student;
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
Syntax : TRUNCATE TABLE table_name;
Example : TRUNCATE TABLE Student;
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Syntax : ALTER TABLE table_name ADD column_name datatype;
Example : ALTER TABLE Student ADD email varchar(255);
Syntax : ALTER TABLE table_name DROP COLUMN column_name;
Example : ALTER TABLE Student DROP COLUMN email varchar(255);
Syntax : ALTER TABLE table_name MODIFY COLUMN column_name;
Example : ALTER TABLE Student MODIFY COLUMN dob year;
The SELECT statement is used to select data from a database.
Syntax : SELECT COL1, COL2, .... FROM TABLE_NAME;
Example : SELECT * FROM STUDENT;
Example : SELECT ID, NAME, AGE FROM STUDENT;
Syntax : SELECT DISTINCT column1, column2, ... FROM table_name;
Example : SELECT DISTINCT City FROM Country;
Syntax : SELECT column1, column2, ... FROM table_name WHERE Condition ;
Example : SELECT * FROM Country WHERE City = 'New York';
Syntax : SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
Example : SELECT * FROM Product ORDER BY price;
Example : SELECT * FROM Product ORDER BY price ASC;
Example : SELECT * FROM Product ORDER BY price DESC;
Syntax : SELECT column1, column2, ... FROM table_name WHERE Condition1 AND Condition2 ....;
Example : SELECT * FROM Customers WHERE Country = 'Spain' AND Name = 'Ramu';
Syntax : SELECT column1, column2, ... FROM table_name WHERE Condition1 OR Condition2 ....;
Example : SELECT * FROM Customers WHERE Country = 'Spain' OR Name = 'Ramu';
Syntax : SELECT column1, column2, ... FROM table_name WHERE NOT Condition1;
Example : SELECT * FROM Customers WHERE NOT Name = 'Ramu';
Syntax : INSERT INTO table_name VALUES (value1, value2, value3, ...);
Syntax : INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Example : INSERT INTO Employee(Name, Address, City, ZipCode) VALUES ('Cardinal', 'Skagen 21', 'Stavanger', '4006');
Example : INSERT INTO Employee VALUES ('Cardinal', 'Skagen 21', 'Stavanger', '4006');
Syntax : SELECT column_names FROM table_name WHERE column_name IS NULL;
Syntax : SELECT Name,Address,City FROM Student WHERE Address IS NULL;
Syntax : SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
Example : SELECT Name,Address,City FROM Student WHERE Address IS NOT NULL;
Syntax : UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example : UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
Syntax : DELETE FROM table_name WHERE condition;
Example : DELETE FROM Customers WHERE CustomerName='Alfreds';
Syntax : SELECT column_name(s) FROM table_name WHERE condition LIMIT number;
Example : SELECT * FROM Customers LIMIT 3;
Example : SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;
Example : SELECT * FROM Customers ORDER BY CustomerName DESC LIMIT 3;
Syntax : SELECT MIN(column_name) FROM table_name WHERE condition;
Example : SELECT MIN(Price) FROM Products;
Example : SELECT MIN(Price), CategoryID FROM Products GROUP BY CategoryID;
Syntax : SELECT MAX(column_name) FROM table_name WHERE condition;
Example : SELECT MAX(Price) FROM Products;
Example : SELECT MAX(Price), CategoryID FROM Products GROUP BY CategoryID;
Syntax : SELECT COUNT(column_name) FROM table_name WHERE condition;
Example : SELECT COUNT(*) FROM Products;
Example : SELECT COUNT(ID) FROM Products;
Example : SELECT COUNT(ID) FROM Products WHERE Price > 20;