SQL in Action

SQL in Action

Introduction

Structured Query Language (SQL) is a language for querying and manipulating data. It is a standard language for relational database management systems (RDBMS). SQL is based on relational algebra and tuple relational calculus. The language is consists of many sublanguages, such as data definition language (DDL), data manipulation language (DML), data control language (DCL), and transaction control language (TCL).

The language SQL falls into the category of declarative languages. It is a non-procedural language, which means that it does not describe how to perform a task, but rather what the task is.

Types of SQL Commands

Types of SQL Commands
Types of SQL Commands

SQL commands are used to perform different operations on the database. The operations range from creating a database to creating a table, inserting data into a table, updating data in a table, granting permissions to users, and so on. The following are the types of SQL commands:

  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Data Control Language (DCL)
  4. Data Query Language (DQL)
  5. Transaction Control Language (TCL)

Data Definition Language (DDL)

DDL is used to define the database structure or schema. This includes creating and modifying objects in the database. The following are the DDL commands:

CREATE

The CREATE command is used to create the database or its objects (like table, index, function etc). The following SQL command creates a table named "books" in the "library" database.

CREATE TABLE library.books (
    book_id INT NOT NULL,
    book_name VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (book_id)
);

ALTER

The ALTER command is used to alter the structure of the database. It is also used to add and drop various constraints on the table. The following SQL command adds a new column named "publisher" to the "books" table.

ALTER TABLE library.books ADD publisher VARCHAR(100) NOT NULL;

DROP

The DROP command is used to delete objects from the database. The following SQL command deletes the "books" table from the "library" database.

DROP TABLE library.books;

TRUNCATE

The TRUNCATE command is used to remove all records from a table, including all spaces allocated for the records are removed. The following SQL command deletes all the records from the "books" table.

TRUNCATE TABLE library.books;

COMMENT

The COMMENT command is used to add comments to the table or database objects. The following SQL command adds a comment to the "books" table.

COMMENT ON TABLE library.books IS 'This table contains the list of books in the library.';

RENAME

The RENAME command is used to rename an object existing in the database. The following SQL command renames the "books" table to "book_list".

RENAME TABLE library.books TO library.book_list;

Data Manipulation Language (DML)

DML is used to manipulate the data present in the database. It includes inserting, deleting, and modifying data in the database. Most of the DML commands are used to manipulate data in a single table. The following are the DML commands:

INSERT

The INSERT command is used to insert data into a table. The following SQL command inserts a new record in the "books" table.

INSERT INTO library.books (book_id, book_name, author, price, publisher)
VALUES (1, 'The Alchemist', 'Paulo Coelho', 200.00, 'HarperCollins');

The previous SQL command can also be written as:

INSERT INTO library.books
VALUES (1, 'The Alchemist', 'Paulo Coelho', 200.00, 'HarperCollins');

UPDATE

The UPDATE command is used to modify the existing records in a table. The following SQL command updates the price of the book whose id is 1.

UPDATE library.books
SET price = 250.00
WHERE book_id = 1;

DELETE

The DELETE command is used to delete records from a table. The following SQL command deletes the book whose id is 1.

DELETE FROM library.books
WHERE book_id = 1;

LOCK

The LOCK command is used to lock a table against any changes. The following SQL command locks the "books" table.

LOCK TABLE library.books IN EXCLUSIVE MODE;

Data Control Language (DCL)

DCL is used to define the security of the database. It includes granting and revoking permissions to the users. The following are the DCL commands:

GRANT

The GRANT command is used to grant permissions to the users. The following SQL command grants the SELECT permission on the "books" table to the user "user_1".

GRANT SELECT ON library.books TO user_1;

REVOKE

The REVOKE command is used to revoke permissions from the users. The following SQL command revokes the SELECT permission on the "books" table from the user "user_1".

REVOKE SELECT ON library.books FROM user_1;

Data Query Language (DQL)

DQL is used to query data from the database. It is used to fetch data from a single table or multiple tables. The following are the DQL commands:

SELECT

The SELECT command is used to fetch data from a database. The following SQL command fetches all the records from the "books" table.

SELECT * FROM library.books;

Transaction Control Language (TCL)

TCL is used to control the transactions in the database. When a transaction is started, it is in temporary state. TCL commands are used to make the changes permanent or to cancel the transaction. The following are the TCL commands:

COMMIT

The COMMIT command is used to commit the changes made by the transaction. The following SQL command commits the changes made by the transaction.

COMMIT;

ROLLBACK

The ROLLBACK command is used to rollback the changes made by the transaction. The following SQL command rolls back the changes made by the transaction.

ROLLBACK;

SAVEPOINT

The SAVEPOINT command is used to create a savepoint within a transaction. The following SQL command creates a savepoint named "savepoint_1".

SAVEPOINT savepoint_1;

SQL Operators

SQL provides various operators to perform different operations on data. The following table lists the most commonly used operators.

Arithmetic Operators

Arithmetic operators are used to perform arithmetic operations on numeric data.

OperatorDescriptionExample
+adds two operandsSELECT 10 + 5 AS sum;
-subtracts two operandsSELECT 10 - 5 AS difference;
*multiplies two operandsSELECT 10 * 5 AS product;
/divides two operandsSELECT 10 / 5 AS quotient;
%returns the remainderSELECT 10 % 5 AS remainder;

Comparison Operators

Comparison operators are used to compare two values. The result of a comparison is always a boolean value.

OperatorDescriptionExample
=is equalSELECT 10 = 5 AS result;
<>is not equalSELECT 10 <> 5 AS result;
>is greater thanSELECT 10 > 5 AS result;
<is less thanSELECT 10 < 5 AS result;
>=is greater than or equal toSELECT 10 >= 5 AS result;
<=is less than or equal toSELECT 10 <= 5 AS result;

Logical Operators

Logical operators are used to combine two or more conditions. The result of a logical operation is always a boolean value. The following table lists the logical operators.

OperatorDescriptionExample
ANDandSELECT 10 > 5 AND 10 < 15;
ORorSELECT 10 > 5 OR 10 < 5;
NOTnotSELECT NOT 10 > 5;
BETWEENbetweenSELECT 10 BETWEEN 5 AND 15;
INinSELECT 10 IN (5, 10, 15);
LIKElikeSELECT 'abc' LIKE 'a%';
EXISTSexistsSELECT EXISTS (SELECT * FROM library.books);
ALLallSELECT 10 > ALL (SELECT price FROM library.books);
ANYanySELECT 10 > ANY (SELECT price FROM library.books);
SOMEsomeSELECT 10 > SOME (SELECT price FROM library.books);

Bitwise Operators

Bitwise operators are used to perform bitwise operations on integer data.

OperatorDescriptionExample
&andSELECT 10 & 5 AS result;
^xorSELECT 10 ^ 5 AS result;
~notSELECT ~10 AS result;

Basic SQL Commands

The following are the basic SQL commands:

Create

The CREATE command is used to create the database or its objects (like table, index, function etc).

The following SQL command creates a database named "library".

CREATE DATABASE library;

The following SQL command creates a table named "books" in the "library" database.

CREATE TABLE library.books (
    book_id INT NOT NULL,
    book_name VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (book_id)
);

Insert

The INSERT command is used to insert data into a table. The following SQL command inserts a new record in the "books" table.

INSERT INTO library.books (book_id, book_name, author, price, publisher)
VALUES (1, 'The Alchemist', 'Paulo Coelho', 200.00, 'HarperCollins');

When inserting data into a table, you can specify the column names or omit them. If you omit the column names, the values will be inserted in the same order in which the columns are defined in the table.

Select

The SELECT command is used to fetch data from a database. The following SQL command fetches all the records from the "books" table.

SELECT * FROM library.books;

To fetch only specific columns, you can specify the column names separated by commas.

SELECT book_id, book_name, author FROM library.books;

To fetch only distinct values, you can use the DISTINCT keyword. This keyword is used to return only distinct (different) values.

SELECT DISTINCT author FROM library.books;

Where

To filter the records, you can use the WHERE clause. The WHERE clause is used to specify a condition while fetching the data. The following SQL command fetches the books written by "Paulo Coelho".

SELECT * FROM library.books
WHERE author = 'Paulo Coelho';

The WHERE clause can be used with comparison operators like =, >, <, >=, <=, <> (not equal to) etc.

SELECT * FROM library.books
WHERE price >= 200.00;

Order By

The ORDER BY clause is used to sort the result-set in ascending or descending order. By default, the ORDER BY clause sorts the result-set in ascending order. The following SQL command fetches all the books sorted by their price in descending order.

SELECT * FROM library.books
ORDER BY price DESC;

You can sort the result-set by multiple columns. The following SQL command fetches all the books sorted by their author and then by their price in descending order.

SELECT * FROM library.books
ORDER BY author, price DESC;

Limit

The LIMIT clause is used to specify the maximum number of records to be fetched from the database. The following SQL command fetches only the first 5 books from the "books" table.

SELECT * FROM library.books
LIMIT 5;

To fetch a certain number of records starting from a particular offset, you can use the OFFSET clause. The following SQL command fetches 5 books starting from the 3th book.

SELECT * FROM library.books
LIMIT 5 OFFSET 2;

This is equivalent to the following SQL command.

SELECT * FROM library.books
LIMIT 2, 5;

Alias

Alias is used to give a table, or a column in a table, a temporary name. The AS keyword is used to create an alias. The following SQL command fetches all the books and gives the "books" table an alias name "b".

SELECT * FROM library.books AS b;

The following SQL command fetches the book name and price of all the books and gives the "book_name" and "price" columns an alias name "name" and "cost" respectively.

SELECT book_name AS name, price AS cost FROM library.books;

Group By

The GROUP BY clause is used to group the result-set by one or more columns. The following SQL command fetches the number of books written by each author.

SELECT author, COUNT(*) AS num_books
FROM library.books
GROUP BY author;

Having

The HAVING clause is used with the GROUP BY clause to filter the grouped records. The following SQL command fetches the number of books written by each author having more than 1 book.

SELECT author, COUNT(*) AS num_books
FROM library.books
GROUP BY author
HAVING COUNT(*) > 1;

SQL Commands for Modifying Data

The following are the SQL commands for modifying data:

Update

The UPDATE command is used to modify the existing records in a table. The following SQL command updates the price of all the books written by "Paulo Coelho".

UPDATE library.books
SET price = price + 10.00
WHERE author = 'Paulo Coelho';

Delete

The DELETE command is used to delete existing records from a table. The following SQL command deletes all the books written by "Paulo Coelho".

DELETE FROM library.books
WHERE author = 'Paulo Coelho';

Alter

The ALTER command is used to alter the structure of the database. The command can be used to add, delete or modify columns in a table. The following SQL command adds a new column named "publisher" in the "books" table.

ALTER TABLE library.books
ADD publisher VARCHAR(100) NOT NULL;

SQL Commands for Database Administration

The following are the SQL commands for database administration:

Drop

The DROP command is used to delete objects from the database. The command can be used to drop a database, table, view, index etc. The following SQL command drops the "books" table.

DROP TABLE library.books;

Truncate

The TRUNCATE command is used to delete all the records from a table. The following SQL command deletes all the records from the "books" table.

TRUNCATE TABLE library.books;

References

Related Posts