In this article, we will learn about tables in SQL in detail. If you are new to SQL, don’t worry, this is the perfect article for you to learn all about tables in SQL.
Introduction
Data- it is a fact related to any entity in consideration.
Database- A database is a systematic collection of data. They support the storage and manipulation of data.
A database is a collection of tables. Each table is made of a series of columns. Data is stored in rows. The place where a row intersects with columns is called a field. Database Management Systems is a collection of program which enables its users to access database, manipulate data, and report or represents data.
Relational Database- it is a database made up of tables and columns that relate to one another. The relationships are based on a key that is contained in a column.
SQL:
SQL is a structured Query Language and it is a standard language used for managing data held in RDBMS.
SQL Advantage:
We can access many records in a single command. It eliminates the need to specify how to reach a record.
DDL:
DDL is an abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in the database.
DDL statements in SQL:
CREATE- creates objects in the database.
ALTER- alters objects of the database.
DROP- deletes objects of the database.
TRUNCATE- deletes all records from a table.
RENAME- renames tables/views to new names.
DML:
DML is an abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, and update data in the database.
DML statements in SQL:
SELECT- retrieves data from a table.
INSERT- inserts data into a table.
UPDATE- updates existing data into a table.
DELETE- deletes all records from a table.
REPLACE- inserts data into a table and if it already exists then deletes and inserts.
TCL:
TCL is an abbreviation of Transactional Control Langauge. It is used to manage different transactions occurring within a database.
TCL statement in SQL:
BEGIN – starts the transaction.
COMMIT – saves work done in a transaction.
ROLLBACK – restores the database to its original state since the last COMMIT command in transactions.
DCL:
DCL is an abbreviation of Data Control Language. It is used to create roles, permissions, and control access to the database.
DCL statements in SQL:
GRANT – gives the user access privileges to the database
REVOKE- withdraw the user’s access privileges to the database given with the GRANT command
SQL database:
SQL Server manages data by performing storage, retrieval, and manipulation of database records.
Records are organized into tables and tables are organized into databases.
SQL stores the database’s common location called a “data directory”. The name of the subdirectory is the same as the database name.
Creating Database:
CREATE DATABASE [IF NOT EXISTS] database_name;
Followed by the CREATE DATABASE statement is the database name that you want to create.
It is recommended that the database name should be as meaningful and descriptive as possible.
If we try to create a database that already exists an error occurs. With the IF NOT EXISTS clause, the statement creates the database only if it does not already exist else the statement does nothing and no error occurs.
Showing Database:
SHOW DATABASES;
The SHOW statement displays all databases in the SQL database server. You can use the SHOW statement to check the database that you have created or to see all the database servers before you create a new database.
SHOW DATABASES can take a LIKE ‘pattern’ clause. With LIKE, the statement performs a pattern-matching operation and displays information only about databases with names that match the pattern.
Using & finding databases:
USE:
USE database_name;
Creating a database has no effect on which database currently is selected as the default database.
Before working with a particular database, you must tell SQL which database you want to work with by using the USE statement.
SELECT:
SELECT database();
This can be used to retrieve the current database being selected.
Dropping Database:
DROP DATABASE[IF EXISTS];
Following the DROP DATABASE is the database name that you want to remove.
Similar to the CREATE DATABASE statement, the IF EXISTS is an optional part of the statement to prevent you from removing a database that does not exist in the database server.
DROP DATABASE does not require the database to be empty. SQL removes all objects that it contains: tables, stored routines, and triggers.
Renaming Database:
We cannot rename a database. One way to do this is to:
– Dump the database
– Create a database with a new name
– Reload the data into the new database
– Drop the old database
Schema:
Another database word is the schema.
Statements that use the database keyword can be written using schema also.
To add a column to the table:
ALTER TABLE table_name ADD[COLUMN] column_name data_type;
To add multiple columns:
ALTER TABLE table_name ADD[COLUMN] column_name1 data_type, ADD[COLUMN] column_name2 data_type;
To add a column to the first position:
ALTER TABLE table_name ADD[COLUMN] column_name data_type FIRST;
To add a column after a particular column:
ALTER TABLE table_name ADD [COLUMN] column_name Data_type AFTER existing_column_name;
To change the datatype of a column:
ALTER TABLE table_name MODIFY existing_col_name new_datatype;
To change multiple columns datatypes:
ALTER TABLE table_name MODIFY existing_col_name_1 new_datatype, MODIFY existing_col_name_2 new_datatype;
Note: SQL won’t allow us to change a column’s datatype if that change would cause the data to be lost.
To change column attributes:
ALTER TABLE table_name MODIFY existing_col_name datatype column_attributes;
To change multiple column attributes:
ALTER TABLE table_name MODIFY existing_col_name_1 datatype column_attributes; MODIFY existing_col_name_2 datatype column_attributes;
Note: SQL won’t allow us to change a column’s datatype if that change would cause the data to be lost.
To rename a column:
ALTER TABLE table_name CHANGE old_col_name new_col_name datatype;
To drop a column:
ALTER TABLE table_name DROP existing_col_name;
To drop multiple columns:
ALTER TABLE table_name DROP existing_col_name_1, DROP existing_col_name_2;
Note: a column cannot be dropped if it’s the only column in the table.
Constraints:
Constraints are used to enforce the integrity of the data in a table by defining rules about the values that can be stored in the columns of the table.
When a constraint is defined at column definition it is called a column-level constraint.
When a constraint is defined at the table level using the CONSTRAINT keyword it’s called a table-level constraint.
A table-level constraint can use multiple columns, a table-level constraint can be provided with some names also.
Unique constraint:
Unique constraint requires that each row has a unique value for the column or columns.
A unique constraint is either a column constraint or table constraint that defines a rule that constrains values in a column or group of columns to be unique.
If we insert or update a value that causes a duplicate value in a unique column, SQL will issue an error message and reject the change.
To define a unique constraint to a column:
CREATE TABLE table_name( column_name );
To define a unique constraint as a table constraint:
CREATE TABLE table_name( …, col_name data_type; …, UNIQUE(col_name) );
If we want to enforce unique values across columns, we must define unique constraints as table constraints and separate each column by a comma.
CREATE TABLE table_name( …, col_name_3 data_type, col_name_4 data_type, …, UNIQUE(col_name) );
We can also assign a name to a unique constraint using the CONSTRAINT clause:
CREATE TABLE table_name( .., col_name_1 data_type, col_name_2 data_type, …, CONSTRAINT );
To see the index we can use:
SHOW INDEX FROM table_name;
To remove the index we can use:
DROP INDEX OR ALTER TABLE table_name DROP INDEX index_name;
To add constraints to the existing table:
ALTER TABLE table_name DROP INDEX index_name;
To add constraints to the existing table:
ALTER TABLE table_name ADD UNIQUE(column_list);
To add a unique constraint with the name:
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(constraint_list);
Primary Key Constraint:
The primary key constraint requires that each row has a unique value for the column or columns and it does not allow a null value.
Whenever a column is declared as primary following things happen:
– The column is forced to be NOT NULL
– The column is moved to contain unique values
– The index is created for the column.
To define a primary key constraint to a column:
CREATE TABLE table_name( …, col_name PRIMARY KEY, … );
To define a primary key constraint as a table constraint:
CREATE TABLE table_name( …, col_name data_type, …, PRIMARY KEY(col_name) );
To define a primary key for multiple columns:
CREATE TABLE table_name( …, col_name_1 data_type col_name_2 data_type, …, PRIMARY KEY(col_name_1, col_name_2) );
To assign a name to the primary key constraint we have to use the table constraint:
CREATE TABLE table_name( …, col_name_1 data_type, col_name_2 data_type, …, CONSTRAINT constraint_name PRIMARY KEY(col_name) );
To see the index we can use:
SHOW INDEX FROM table_name;
To drop the primary key:
ALTER TABLE table_name DROP INDEX index_name;
To add a primary key to an existing table:
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column_name);
Foreign Key Constraint
Foreign key constraint/ reference key constraint requires values in one table to match values in another table. This defines the relationship between two tables and enforces referential integrity.
To create a foreign key, at the table level we write FOREIGN KEY keywords followed by REFERENCES keyword followed by the name of the related table and the name of the related column in parenthesis.
Actions:
When a row from the primary table is updated/deleted:
If we use the CASCADE option, then the delete is cascaded to the related rows in the foreign key table.
If we use the SET NULL option, then the foreign key column of the foreign key table is set to null.
Conclusion
This was all about tables in SQL. I hope you liked this article. Do let us know your feedback in the comments down below.