Developed first in the 1970s by E.F Codd,SQL is today everywhere from Microsoft to Stack Overflow, but what is SQL?
SQL stands for Structured Query Language which is mainly used to manage relational databases .
What is a relational database?
A relational database is a collection of data items with predefined or existing relationships between them.
Such databases are in the form of tables consisting of rows and columns.
To understand more about Relational Databases:What is a Relational Database?
Now let us dive back into SQL,
SQL helps us to interact with Relational Database Management Systems(RDBMS) to store , manipulate and retrieve the data stored in them.These SQL statements are generally referred as queries.
Now let us understand some basic SQL queries:
SQL Queries
CREATE TABLE
We can create a table by using the following syntax:
View Website
View Website
View Website
CREATE TABLE table_name(column_name datatype,column_name_2 datatype);
**Note:**The data type of the columns can differ from one database to another.
For example,if we want to create an Employee Information Table, we would write:
CREATE TABLE EMPLOYEE(ID NUMBER(4),NAME VARCHAR2(15));
INSERT INTO
Now we need some kind of data in the table , we can do this using the following format:
INSERT INTO TABLE_NAME(column_1,column_2) VALUES(value_1,value_2);
So , for the employee table , we can enter data using the following:
INSERT INTO EMPLOYEE(ID,NAME) VALUES(1,’Chris’);
ALTER TABLE
We created the table but forgot to add a Salary column ,what can we do?
We can add the column using ALTER TABLE using the following syntax:
ALTER TABLE table_name ADD column_name datatype;
So,now we can add a Salary column to the Employee table.
ALTER TABLE EMPLOYEE ADD SALARY NUMBER(8);
The ALTER TABLE command also allows us to modify or delete an existing column.
ALTER TABLE table_name MODIFY column_name datatype;
So,in case we need to modify the datatype of the name column, we can write the following:
ALTER TABLE EMPLOYEE MODIFY NAME VARCHAR2(20);
We can also use it to delete an existing column:
ALTER TABLE table_name DROP COLUMN column_name ;
DROP TABLE
The DROP TABLE command allows us to drop the entire table along with its data.
We make use of the following syntax:
DROP TABLE_NAME;
For example:
DROP EMPLOYEE;
TRUNCATE TABLE
The Truncate Table command allows us to remove only the data from the table.
We make use of the following syntax:
TRUNCATE TABLE_NAME;
For example:
TRUNCATE EMPLOYEE;
Now let us see some major as well as useful commands to manipulate the data
SELECT
SELECT statement is mainly used to retrive the data from the database
It is very useful to retrieve the data based upon our conditions.
The syntax would be:
SELECT expression FROM table_name;
So,here if we want to see the names of the employees:
SELECT NAME FROM EMPLOYEE;
To retrieve all the data from the table , then we would write:
SELECT * FROM EMPLOYEE
Here, * denotes all the fields.
DISTINCT
In some cases, we might have a lot of repeated data so to identify the unique data , we can use DISTINCT command
The syntax would be:
SELECT DISTINCT column_name ,column_name FROM table_name;
For example:
SELECT DISTINCT ID,NAME FROM EMPLOYEE;
WHERE
The WHERE clause is another useful query in filtering out the data from the tables.
The syntax would be:
SELECT FROM table_name WHERE condition;
Let us take the example from the Employee Table.
Suppose we want to find the information of employees whose salary is greater than 30000 , then we would write:
SELECT * FROM EMPLOYEE WHERE SALARY>30000;
Constraints
What are Constraints ?
Constraints in SQL are rules applied on the table to decide which type of data can be entered .
These constraints help in limiting the data that can be stored.
There are mainly 6 constraints which are as follows:
NOT NULL
This constraint simply defines that we cannot keep the data empty in the column i.e. null.
The syntax would be:
CREATE TABLE table_name(column_name datatype NOT NULL,column_name datatype);
An example would be:
CREATE TABLE EMPLOYEE(ID NUMBER(5),NAME VARCHAR2(20) NOT NULL);
UNIQUE
This constraint ensures that the data in the columns is distinct(or different).
The syntax would be:
CREATE TABLE table_name(column_name datatype UNIQUE ,column_name datatype );
For example:
CREATE TABLE EMPLOYEE(ID NUMBER(5) UNIQUE,NAME VARCHAR2(20));
PRIMARY KEY
A primary key constraint ensures that each and every record/row is uniquely identified.
A table can have only one primary key.
To add ,a primary key we would write:
CREATE TABLE EMPLOYEE(ID NUMBER(8) NOT NULL,NAME VARCHAR2(20) NOT NULL,SALARY DECIMAL(10, 2),PRIMARY KEY(ID));
To add a primary key to an existing table:
ALTER TABLE EMPLOYEE ADD CONSTRAINT PKEY_ID PRIMARY KEY(ID);
FOREIGN KEY
A Foreign Key is mainly used to link two different tables. It is a field in one table and refers to the Primary Key on another table.
To add a foreign key , we would write:
CREATE TABLE MARKETING(ID NUMBER(6),M_ID NUMBER(6),NAME NOT NULL,PRIMARY KEY (M_ID),FOREIGN KEY (ID) REFERENCES EMPLOYEE(ID));
In the example, we use the REFERENCES keyword to link the two tables.
To add a foreign key to an existing table:
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(ID) REFERENCES EMPLOYEE(ID);
CHECK
The CHECK constraint is used in validating the data inserted in the table based on certain conditions.
The syntax would be:
CREATE TABLE table_name(column_1 datatype,column_2 datatype,CHECK condition);
For example:
CREATE TABLE MARKETING(M_ID NUMBER(6),NAME NOT NULL,SALARY NUMBER(8),CHECK(SALARY>20000));
DEFAULT
The default constraint is used to keep a default value for the column.
It is inserted if no other value is specified for the column.
The syntax would be:
CREATE TABLE table_name( column_1 datatype,column_2 datatype DEFAULT 'value');
For example:
CREATE TABLE MARKETING(M_ID NUMBER(6),NAME NOT NULL,SALARY NUMBER(8),CITY VARCHAR2(20) DEFAULT ‘Mumbai’);
Conclusion
Thank you for reading! I hope this article helped you in getting started with SQL.
Image Credits
- https://slickr.vercel.app
- https://www.freepik.com
Comments (0)