SQL basics covered in 10 minutes

SQL stands for Structured Query Language and is used to access and manipulate the database.

RDBMS stands for Relational Database Management System. It is the basis for SQL, and for database systems such as MS SQL Server, Oracle, MySQL, and Microsoft Access.

1. In RDBMS, the data is stored in database objects called tables. A table is a collection of related entries and consists of columns and rows.
2. Every table has smaller entities called fields. A field is a column in a table that stores specific information about every entry in a particular table.
3. A record is each individual entry that exists in a table. It is a horizontal entity in a table.
4. A database most often contains one or more tables. Each table has a unique name.

For this article, I’ll be using MySQL db.

Database commands

1. List all the databases: ‘show databases;” command is used.

2. Create a new database: For creating new database, use

CREATE DATABASE database_name;

In the above screenshot, I created a new db named ‘dummy’.

3. Deleting an existing database: if you want to delete an existing database, use

drop database database_name;

In the above screenshot, I deleted the db named ‘dummy’.

4. Switch to a particular database:

use database_name;

Table Commands

1. Show existing tables in database: To list down all the tables in a database, use

show tables;

2. Create a new table: For creating a new table, use:

CREATE TABLE table_name (
   id INTEGER PRIMARY KEY,
   name TEXT,
   age INTEGER
);

3. Create table using another table: A copy of an existing table can also be created using CREATE TABLE. All columns or specific columns can be selected from an existing table. If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

4. Delete an existing table:

DROP TABLE table_name;

In the above screenshot, I deleted the table named ‘dummy’.

5. Deleting the data from a table:

TRUNCATE TABLE table_name;

In the above screenshot, there was 1 record in table ‘dummy’. After truncating, the table got empty.

6. Altering an existing table: It is used to add, delete, or modify columns in an existing table. It can also be used to add and drop various constraints on a table.

ALTER TABLE table_name ADD column_name datatype;

I added a new column ‘location’ in an existing table.

For dropping a column, use the following command

ALTER TABLE table_name DROP COLUMN column_name;

For changing the data type of a particular column:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

7. Adding the data into a table

It is used to insert new data into the table.

INSERT INTO table_name (column_name) VALUES (values);

Constraints

Constraints are used to specify some rules for the data in the table. They limit the type of data that can be inserted into a table. The constraints help in ensuring the accuracy and reliability of the data in the table. These can be applied on column level or table level.

Most commonly used constraints:

• NOT NULL - column cannot have a NULL value
• UNIQUE - all values in a column are different
• PRIMARY KEY - combination of a NOT NULL and UNIQUE
• FOREIGN KEY - Prevents actions that would destroy links between two tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database quickly

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

Select Command

It is used to select the data from a table. If you want to select specific columns from a table, then use the following command

SELECT column1, column2, ...
FROM table_name;

If you want to select all the data from a table:

SELECT * FROM table_name;

Select Distinct Values

It is used to return only distinct values present in a column.

SELECT DISTINCT column_name FROM table_name;

Where Command

It is used to select data based on specific conditions or filters.

SELECT column_name FROM table_name WHERE condition;

You can also use AND or OR to specify multiple filters

Following operators can be used in the where clause: =, >, <, ≥, ≤, !=, BETWEEN, LIKE, IN

Count command

Gives the number of records that follows a specified condition.

SELECT COUNT(column_name) FROM table_name WHERE condition;

Order By Command

It is used to sort the result in either ascending (default) or descending order of a column’s values.

SELECT "column_name" FROM "table_name" WHERE "condition" ORDER BY column_name ASC|DESC

Updating the existing records in the table

Update the information for data inside the table.

UPDATE table_name SET name = "new_name" WHERE name = "old_name";

If you remove the WHERE clause, all the records will be updated!

Delete the records

Delete a row from the database.

DELETE FROM table_name WHERE condition;

If you omit the WHERE clause, all records in the table will be deleted!

Select Into Command

It copies the data from one table into another table.

SELECT column_name(s) INTO new_table FROM old_table WHERE condition;

The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause. MySQL doesn’t support Select Into. Instead it has Insert Into.. Select.

Select Top

It is used to specify the number of records from a table to display.

• Not all database systems support the SELECT TOP . MySQL supports the LIMIT to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

SELECT column_name FROM table_name WHERE condition LIMIT number;
SELECT TOP number|percent column_name FROM table_name WHERE condition;

Stored Procedure

It is the code that you can save so that it can reused again.

For storing it:

CREATE PROCEDURE procedureName AS sqlStatement GO;

To call a stored procedure:

EXEC procedureName;

Joins

It is used to combine rows from two or more tables based on a common column between them. There are four types of joins.

• INNER JOIN — returns all the common records between both tables
• LEFT (OUTER) JOIN — Returns all rows from the left table, and the matched rows from the right table
• RIGHT (OUTER) JOIN — Returns all rows from the right table, and the matched rows from the left table
• FULL (OUTER) JOIN — Returns all rows when there is a match in ONE of the tables

SELECT column_name FROM table_name_1 INNER JOIN table_name_2 ON table_name_1.column_name = table_name_2.column.name;

One table has a column containing the foreign key for another table, like if you had a table of student personal details and a table of students marks, the table of student details might have a column of roll_nos. You then join that column on the roll_nos column of the other table.

SELECT column_name FROM table_name_1 LEFT JOIN table_name_2 ON table_name_1.column_name = table_name_2.column.name;
SELECT column_name FROM table_name_1 RIGHT JOIN table_name_2 ON table_name_1.column_name = table_name_2.column.name;
SELECT column_name FROM table_name_1 FULL OUTER JOIN table_name_2 ON table_name_1.column_name = table_name_2.column.name;

Self Join

A self join is similar to a regular join, but the table is joined with itself.

SELECT column_name FROM table_name_1 T1, table_name_1 T2 WHERE condition;

Example:

SELECT X.StudentName AS StudentName1, Y.StudentName AS StudentName2, X.City FROM Students X, Students Y WHERE X.StudentID <> Y.StudentID AND X.City = Y.City ORDER BY X.City;

MIN and MAX functions

These functions can be used to get minimum value or a maximum value of a particular column.

SELECT MIN(column_name) FROM table_name WHERE condition;
SELECT MAX(column_name) FROM table_name WHERE condition;

Primary Key

Primary key is used to uniquely identify each record in a particular table. It must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns.

A primary key can be created while creating the table or using alter table command.

CREATE TABLE Students ( ID int NOT NULL,
    LastName varchar(64) NOT NULL,
    FirstName varchar(64),
    Age int,
    PRIMARY KEY (ID)
);

For adding primary key on multiple fields:

CREATE TABLE Students  (
    ID int NOT NULL,
    LastName varchar(64) NOT NULL,
    FirstName varchar(64),
    Age int,
    CONSTRAINT S_Student PRIMARY KEY (ID,LastName)
);

If you want to add primary key using alter table:

ALTER TABLE Students ADD PRIMARY KEY (ID);
ALTER TABLE Students ADD CONSTRAINT S_Student PRIMARY KEY (ID,LastName);

Delete the primary key constraint:

ALTER TABLE Students DROP PRIMARY KEY;

Foreign Key

It is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field(s) in one table, that refers to the PRIMARY KEY in another table.

CREATE TABLE Scores(
    SubjectID int NOT NULL,
    Scores int NOT NULL,
    ID int,
    PRIMARY KEY (SubjectID),
    FOREIGN KEY (ID) REFERENCES Students(ID)
);

Auto Increment Field

It generates a unique number automatically whenever a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

CREATE TABLE Students (
    Student_id int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Student_id)
);

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record that is inserted.

To let the AUTO_INCREMENT sequence start with some other value, use the given command:

ALTER TABLE Students AUTO_INCREMENT=50;

These are the basics on how to create a database, a table, perform CRUD operations on it, and change things in it.

Thanks for reading! Please feel free to leave any feedback.