SQL

SQL, stands for structured query language, is a domain-specific declarative programming language based on tuple relational calculus and relational algebra. It is used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). RDBMS is a relational database management…

SQL consists of many types of statements which are classified in five types of sub-languages- Data Definition Language (DDL): to create and modify the structure of the database. All the commands of DDL are auto-committed which means it permanently saves all the changes in the database.DDL Commands: Data Manipulation Language (DML): DML commands are used…

To create a database in SQL the CREATE DATABASE statement is used. The database name should be unique and less than 128 characters. To create a database in SQL and SQL Server use the command- CREATE DATABASE database_name; Example- CREATE DATABASE student_db; To view database in SQL use command- SHOW DATABASE; To Create or Replace…

DROP DATABASE   To drop an existing SQL database use command- DROP DATABASE database_name; HOW TO DROP DATABASE IN T-SQL DROP DATABASE [IF EXISTS] database_name(s); Example- DROP DATABASE [ IF EXISTS] database_name1 , database_name2, database_nameN; Note- We are required to keep a database backup if we need to recover it in the future. We are…

CREATE TABLE CREATE TABLE statement is used to create a new table in the database. A table definition consists of the table name, a list of columns, their data types, default values, and any integrity constraints as shown in below SQL server syntax – CREATE TABLE <table_name1> ( <column_name1> <data_type> <default_value> <identity_specification> <column_constraint>, <column_name2> <data_type>…

DROP TABLE, TRUNCATE TABLE AND DELETE IN SQL Key Word Syntax Explanation DROP TABLE DROP TABLE <table_name>; DROP TABLE is used to delete a table definition and all data from a table TRUNCATE TABLE TRUNCATE TABLE <table_name>; TRUNCATE TABELE is used to remove all rows or complete data from a table. It doesn’t delete the…

ALTER TABLE IN SQL ALTER TABLE statement is used to add, delete, or modify columns and add or drop constraints on existing table. ALTER TABLE- ADD COLUMN Syntax- ALTER TABLE <table_name> ADD <column_name> <datatype>; Example- Following SQL will add DOB column to existing msstudentsnew table- ALTER TABLE msstudentsnew ADD DOB date; ALTER TABLE – ALTER…

INSERT INTO INSERT INTO statement is used to insert values or records into table in SQL. Records in a table can be added by specifying both the column names and their values that need to be updated. Syntax- INSERT INTO <table_name>(column1, column2, column3, …) VALUES (value1, value2, value3, …); Records in a table can also…

UPDATE UPDATE statement is used to update the existing records in a table in SQL. Syntax- UPDATE <table_name> SET column1 = value1, column2 = value2, … WHERE <condition>; Example- UPDATE mstudentsnew SET st_name = ‘Ajai’ WHERE st_id = 1; Note- IF WHERE Clause is not used it will update all the records.

SELECT SELECT statement is used to access records from a database. The table which stores the record returned by the SELECT statement is called a result-set table. Syntax- SELECT column_name1, column_name2, …column_nameN FROM <table_name>; Description- The column_name1, column_name2, …column_nameN are the name of those columns in the table whose data we want to read or access. Syntax-…

SELECT DISTINCT The SQL DISTINCT command is used with SELECT key word to retrieve only distinct or unique values. Syntax- SELECT DISTINCT column_name1, column_name2, …column_nameN FROM <table_name>; Description- The distinct values from column_name1, column_name2, …column_nameN will be retrieved. It will avoid duplicate data.

WHERE WHERE clause in SQL is a data manipulation language (DML) statement. WHERE clause is used in filter the records based on satisfying specific condition(s). Syntax- SELECT column_name1, column_name2, …column_nameN FROM <table_name> WHERE <condition>; Example- SELECT CustomerName FROM Customers WHERE Country=‘Germany’; Description- The records having values from column_name1, column_name2, …column_nameN will be filtered and retrieved from table_name on meeting the condition provided…

AND OPERATOR IN SQL The AND operator displays a record if all the conditions separated by AND are TRUE. Syntax- SELECT column1, column2, columnN FROM <table_name> WHERE <condition1>AND <condition2>…AND <conditionN>; OR OPERATOR IN SQL The OR operator displays a record if any of the conditions separated by OR is TRUE. Syntax- SELECT column1, column2, columnN FROM…

ORDER BY CLAUSE ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. Syntax to sort the records in ascending order: SELECT ColumnName1,…,ColumnNameN FROM <TableName> ORDER BY <ColumnName> ASC;    Syntax to sort the records in descending order: SELECT ColumnName1,…,ColumnNameN FROM <TableName>  ORDER BY <ColumnName>DESC;    Syntax to sort the records in ascending order without…

A field having NULL value is one that has been left blank during record creation, NULL value field means field with no value. IS NULL IS NULL operator in SQL is used to test for NULL Values . Syntax- SELECT column_name1, column_name2..column_nameN FROM <table_name> WHERE<column_name> IS NULL; IS NOT NULL IS NOT NULL operators in SQL is used to test…

SELECT TOP SELECT TOP clause is used to specify the number of records to return from top of the table. Syntax of TOP clause in SQL server – SELECT TOP number|PERCENT ColumnName1,…,ColumnNameN FROM <TableName> WHERE<condition> ;    Example of TOP clause in SQL server –– To select top 10 records of customers from city Berlin from customers table SELECT TOP 10 * FROM Customers WHERE city=‘Berlin’; Example- To…

MIN() AND MAX() MIN() function returns the smallest value of the selected column Syntax – SELECT  MIN(ColumnName) FROM <TableName> WHERE<condition> ;    Example- SELECT  MIN(Price) FROM Products; MAX() function returns the largest value of the selected column Syntax – SELECT  MAX(ColumnName) FROM <TableName> WHERE<condition> ; Example- SELECT  MAX(Price) FROM Products; Note- Northwind database is used for examples.

COUNT() function returns the number of rows that matches a specified condition. Syntax – SELECT  COUNT(ColumnName) FROM <TableName> WHERE<condition> ;    Example- SELECT  Count(ProductID) FROM Products; AVG() function returns the average value of a numeric column. Syntax – SELECT  AVG(ColumnName) FROM <TableName> WHERE<condition> ; Example- SELECT  AVG(Price) FROM Products; SUM() function returns the sum value of a numeric column. Syntax – SELECT  SUM(ColumnName)…

LIKE operator is used in a WHERE clause to search for a specified pattern in a column. SQL LIKE Operator uses following two wildcard characters in conjuction or independently- 1.% (Percent Sign)- It matches zero, one or more than one characters. 2._ (Underscore Sign)– It matches only one or a single character. Syntax – SELECT(ColumnName(s)) FROM <TableName> WHERE<ColumnName>LIKE <pattern> ;    Examples-…

WILDCARD  characters are used to substitute one or more characters in a string and used with LIKE operator  which is used in a WHERE clause to search for a specified pattern in a column. Syntax in SQL to use Wildcard characters – SELECT(ColumnName(s)) FROM <TableName> WHERE<ColumnName>LIKE <pattern> ;    Examples of wildcard characters use in SQL- Replace the word pattern provided…