SQL
-
WHAT IS 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…
-
WHAT ARE THE SUB LANGUAGES OF SQL?
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…
-
HOW TO CREATE A DATABASE IN SQL?
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…
-
HOW TO DELETE A DATABASE IN SQL?
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…
-
HOW TO CREATE TABLE IN SQL?
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>…
-
HOW TO DROP, TRUNCATE AND DELETE TABLE?
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…
-
HOW TO ALTER TABLE IN SQL?
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…
-
HOW TO DATA INSERT INTO TABLE IN SQL?
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…
-
HOW TO UPDATE EXISTING RECORDS IN TABLE IN SQL?
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.
-
HOW TO USE SELECT IN SQL?
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-…
-
HOW TO SELECT DISTINCT VALUES IN SQL?
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.
-
HOW TO USE WHERE CLAUSE IN SQL?
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…
-
HOW TO USE SQL AND, OR, NOT OPERATORS?
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…
-
HOW TO USE SQL ORDER BY CLAUSE?
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…
-
WHAT IS A NULL VALUE IN SQL?
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…
-
HOW TO USE SELECT TOP CLAUSE IN SQL?
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…
-
HOW TO USE MIN() AND MAX() FUNTION IN SQL?
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.
-
HOW TO USE COUNT() AVG() SUM() FUNTIONS IN SQL?
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)…
-
HOW TO USE LIKE OPERATOR IN SQL?
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-…
-
HOW TO USE WILDCARD CHARACTERS IN SQL?
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…