Download SQL Simplified: Unlocking the Power of Database Management and more Study notes Computer Science in PDF only on Docsity!
SQL Commands
o SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks,
functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission
for users. Types of SQL Commands There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
- Data Definition Language (DDL)
o DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
o All the command of DDL are auto-committed that means it permanently save all the changes in the database.
Here are some commands that come under DDL:
o CREATE
o ALTER
o DROP
o TRUNCATE
a. CREATE It is used to create a new table in the database. Syntax:
- CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]); Example:
- CREATE TABLE EMPLOYEE(Name VARCHAR2( 20 ), Email VARCHAR2( 100 ), DOB DATE); b. DROP: It is used to delete both the structure and record stored in the table. Syntax
- DROP TABLE ; Example
- DROP TABLE EMPLOYEE; c. ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute. Syntax: To add a new column in the table
- ALTER TABLE table_name ADD column_name COLUMN-definition; To modify existing column in the table:
- ALTER TABLE MODIFY(COLUMN DEFINITION....); EXAMPLE
- ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2( 20 ));
- ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2( 20 )); d. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.
For example:
- UPDATE students
- SET User_Name = 'Sonoo'
- WHERE Student_Id = '3' c. DELETE: It is used to remove one or more row from a table. Syntax:
- DELETE FROM table_name [WHERE condition]; For example:
- DELETE FROM javatpoint
- WHERE Author="Sonoo";
- Data Control Language DCL commands are used to grant and take back authority from any database user. Here are some commands that come under DCL:
o Grant
o Revoke
a. Grant: It is used to give user access privileges to a database. Example
- GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER; b. Revoke: It is used to take back permissions from the user. Example
- REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
- Transaction Control Language TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.
Here are some commands that come under TCL:
o COMMIT
o ROLLBACK
o SAVEPOINT
a. Commit: Commit command is used to save all the transactions to the database. Syntax:
- COMMIT; Example:
- DELETE FROM CUSTOMERS
- WHERE AGE = 25 ;
- COMMIT; b. Rollback: Rollback command is used to undo transactions that have not already been saved to the database. Syntax:
- ROLLBACK; Example:
- DELETE FROM CUSTOMERS
- WHERE AGE = 25 ;
- ROLLBACK; c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction. Syntax:
- SAVEPOINT SAVEPOINT_NAME;
- Data Query Language DQL is used to fetch the data from the database. It uses only one command:
o SELECT
The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table. NOTE − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever. Syntax The basic syntax of this DROP TABLE statement is as follows − DROP TABLE table_name;
- INSERT INTO The SQL INSERT INTO Statement is used to add new rows of data to a table in the database. Syntax There are two basic syntaxes of the INSERT INTO statement which are shown below. INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN); Here, column1, column2, column3,...columnN are the names of the columns in the table into which you want to insert the data. You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax will be as follows − INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN); Example The following statements would create six records in the CUSTOMERS table. INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES ( 1 , 'Ramesh', 32 , 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES ( 2 , 'Khilan', 25 , 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES ( 3 , 'kaushik', 23 , 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES ( 4 , 'Chaitali', 25 , 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES ( 5 , 'Hardik', 27 , 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES ( 6 , 'Komal', 22 , 'MP', 4500.00 ); You can create a record in the CUSTOMERS table by using the second syntax as shown below. INSERT INTO CUSTOMERS VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
All the above statements would produce the following records in the CUSTOMERS table as shown below. +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
- The Select Query The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets. Syntax: The basic syntax of the SELECT statement is as follows − SELECT column1, column2, columnN FROM table_name; Here, column1, column2... are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax. SELECT * FROM table_name; Example: Consider the CUSTOMERS table having the following records − +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ The following code is an example, which would fetch the ID, Name and Salary fields of the customers available in CUSTOMERS table. SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS; This would produce the following result − +----+----------+----------+ | ID | NAME | SALARY | +----+----------+----------+ | 1 | Ramesh | 2000.00 | | 2 | Khilan | 1500.00 |
The following code is an example which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 − SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 ; This would produce the following result − +----+----------+----------+ | ID | NAME | SALARY | +----+----------+----------+ | 4 | Chaitali | 6500.00 | | 5 | Hardik | 8500.00 | | 6 | Komal | 4500.00 | | 7 | Muffy | 10000.00 | +----+----------+----------+ The following query is an example, which would fetch the ID, Name and Salary fields from the CUSTOMERS table for a customer with the name Hardik. Here, it is important to note that all the strings should be given inside single quotes (''). Whereas, numeric values should be given without any quote as in the above example. SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE NAME = 'Hardik'; This would produce the following result − +----+----------+----------+ | ID | NAME | SALARY | +----+----------+----------+ | 5 | Hardik | 8500.00 | +----+----------+----------+
- AND and OR Conjunctive Operators The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQL statement. ▪ The AND Operator The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause. Syntax The basic syntax of the AND operator with a WHERE clause is as follows − SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN]; You can combine N number of conditions using the AND operator. For an action to be taken by the SQL statement, whether it be a transaction or a query, all conditions separated by the AND must be TRUE.
Example Consider the CUSTOMERS table having the following records − +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ Following is an example, which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years − SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25 ; This would produce the following result − +----+-------+----------+ | ID | NAME | SALARY | +----+-------+----------+ | 6 | Komal | 4500.00 | | 7 | Muffy | 10000.00 | +----+-------+----------+ ▪ The OR Operator The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause. Syntax The basic syntax of the OR operator with a WHERE clause is as follows − SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN] You can combine N number of conditions using the OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, the only any ONE of the conditions separated by the OR must be TRUE. Example Consider the CUSTOMERS table having the following records − +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 |
| ID | NAME | AGE | ADDRESS | SALARY |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | Pune | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ If you want to modify all the ADDRESS and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough as shown in the following code block. SQL> UPDATE CUSTOMERS SET ADDRESS = 'Pune', SALARY = 1000.00; Now, CUSTOMERS table would have the following records − +----+----------+-----+---------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+---------+---------+ | 1 | Ramesh | 32 | Pune | 1000.00 | | 2 | Khilan | 25 | Pune | 1000.00 | | 3 | kaushik | 23 | Pune | 1000.00 | | 4 | Chaitali | 25 | Pune | 1000.00 | | 5 | Hardik | 27 | Pune | 1000.00 | | 6 | Komal | 22 | Pune | 1000.00 | | 7 | Muffy | 24 | Pune | 1000.00 | +----+----------+-----+---------+---------+
- DELETE Query The SQL DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted. Syntax The basic syntax of the DELETE query with the WHERE clause is as follows − DELETE FROM table_name WHERE [condition]; You can combine N number of conditions using AND or OR operators. Example Consider the CUSTOMERS table having the following records − +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ The following code has a query, which will DELETE a customer, whose ID is 6. SQL> DELETE FROM CUSTOMERS WHERE ID = 6 ; Now, the CUSTOMERS table would have the following records. +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ If you want to DELETE all the records from the CUSTOMERS table, you do not need to use the WHERE clause and the DELETE query would be as follows − SQL> DELETE FROM CUSTOMERS; Now, the CUSTOMERS table would not have any record.
- LIKE Clause The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator.
- The percent sign (%)
- The underscore () The percent sign represents zero, one or multiple characters. The underscore represents a single number or character. These symbols can be used in combinations. Syntax The basic syntax of % and _ is as follows − SELECT FROM table_name WHERE column LIKE 'XXXX%' or SELECT FROM table_name WHERE column LIKE '%XXXX%' or SELECT FROM table_name WHERE column LIKE 'XXXX' or
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ Following is an example, which would display all the records from the CUSTOMERS table, where the SALARY starts with 200. SQL> SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%'; This would produce the following result − +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+----------+-----+-----------+----------+
- WILDCARD Operator The SQL LIKE operator, which is used to compare a value to similar values using the wildcard operators. SQL supports two wildcard operators in conjunction with the LIKE operator which are explained in detail in the following table. Sr.No. Wildcard & Description 1 The percent sign (%) Matches one or more characters. Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character. 2 The underscore (_) Matches one character. Note − MS Access uses a question mark (?) instead of the underscore () to match any one character. The percent sign represents zero, one or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations. Syntax The basic syntax of a '%' and a '' operator is as follows. SELECT * FROM table_name WHERE column LIKE 'XXXX%' or SELECT * FROM table_name
WHERE column LIKE '%XXXX%' or SELECT * FROM table_name WHERE column LIKE 'XXXX_' or SELECT * FROM table_name WHERE column LIKE 'XXXX' or SELECT * FROM table_name WHERE column LIKE 'XXXX' You can combine N number of conditions using the AND or the OR operators. Here, XXXX could be any numeric or string value. Example The following table has a number of examples showing the WHERE part having different LIKE clauses with '%' and '' operators. Sr.No. Statement & Description 1 WHERE SALARY LIKE '200%' Finds any values that start with 200. 2 WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position. 3 WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions. 4
WHERE SALARY LIKE '2_%_%'
Finds any values that start with 2 and are at least 3 characters in length. 5
WHERE SALARY LIKE '%2'
Finds any values that end with 2. 6 WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position and end with a 3.
| 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ The following query is an example on the SQL server, which would fetch the top 3 records from the CUSTOMERS table. SQL> SELECT TOP 3 * FROM CUSTOMERS; This would produce the following result − +----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+ If you are using MySQL server, then here is an equivalent example − SQL> SELECT * FROM CUSTOMERS LIMIT 3 ; This would produce the following result − +----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+ If you are using an Oracle server, then the following code block has an equivalent example. SQL> SELECT * FROM CUSTOMERS WHERE ROWNUM <= 3 ; This would produce the following result − +----+---------+-----+-----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+---------+-----+-----------+---------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+---------+-----+-----------+---------+
- ORDER BY Clause The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default. Syntax The basic syntax of the ORDER BY clause is as follows − SELECT column-list
FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list. Example Consider the CUSTOMERS table having the following records − +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ The following code block has an example, which would sort the result in an ascending order by the NAME and the SALARY − SQL> SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY; This would produce the following result − +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | +----+----------+-----+-----------+----------+ The following code block has an example, which would sort the result in the descending order by NAME. SQL> SELECT * FROM CUSTOMERS ORDER BY NAME DESC; This would produce the following result − +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 7 | Muffy | 24 | Indore | 10000.00 | | 6 | Komal | 22 | MP | 4500.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | +----+----------+-----+-----------+----------+
