How to use SQL DROP DATABASE

DROP DATABASE is use for delete existing SQL database

Syntax

DROP DATABASE databasename

Example

DROP DATABASE dbTest

How to use SQL CREATE DATABASE

CREATE DATABASE is use for create new database in SQL

Syntax

CREATE DATABASE databasename

Example

CREATE DATABASE dbTest

SQL Comments Example

SQL Comments Example

1. Single line comment

-- SELECT ALL CITY RECORD
SELECT * FROM CityMaster

2. Multi line comment

/*
    Return all city
    records from city mater
*/
SELECT * FROM CityMaster

How to use SQL EXISTS operator

EXISTS operator is use for check record exists or not in table and EXISTS operator return true if table contains more than one record

EXISTS Syntax

SELECT ColName FROM TableName WHERE EXISTS (SELECT ColName FROM TableName)

Example

1. IF EXISTS (SELECT TOP 1 CityName FROM CityMaster WHERE CityName = 'TEST')

How to assign grade student mark wise in SQL

Description : In this post how to apply a grade in student table using case when condition

Step 1 : Write below query from update rank in student table

UPDATE tblStudent SET tblStudent.Grade = CASE
    WHEN Mark BETWEEN 0 AND 100 THEN 'C'
    WHEN Mark BETWEEN 101 AND 120 THEN 'B'
    WHEN Mark BETWEEN 121 AND 150 THEN 'A'
    ELSE 'A+' END
FROM tblStudent AS St

Step 2 : Select student table for check result

SELECT * FROM tblstudent

Result is

Student1        100    C
Student2        120    B
Student3        100    C
Student4    150    A
Student5    150    A
Student6    160    A+
Student7    135    A
Student8    129    A
Student9    155    A+

How to use SQL HAVING

HAVING is use with GROUP BY because WHERE condition not use with query

HAVING Syntax

SELECT ColName
FROM TableName
WHERE ColName
GROUP BY ColName
HAVING Condition
ORDER BY ColName

Example

SELECT COUNT(EmpID) FROM EmployeeMaster
GROUP BY Country
HAVING COUNT(EmpID) > 1

above query return number of employee in each country (only add countries with more than 1 employee)

How to use SQL GROUP BY

GROUP BY statement is use with SQL Aggregate function like COUNT, MIN, MAX, AVG, SUM for get result in one or more column or row

GROUP BY Syntax

SELECT ColName
FROM TableName
WHERE Condition
GROUP BY ColName
ORDER BY ColName

Example

1. SELECT COUNT(CountryName) FROM City GROUP BY CountryName

2. SELECT COUNT(CountryName) FROM City GROUP BY CountryName ORDER BY COUNT(CountryName)