Basic SQL Commands Part 1:

SQL is a standard language for accessing databases. SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language.

Create Customers Table as below :-

Orders Table as Below:-

Employee Table as Below:-

 

CREATE DATABASE

The SQL CREATE DATABASE statement is used to create new SQL database.

Always database name should be unique within the RDBMS.

/*Select  and Create Database */

USE mwd;

CREATE DATABASE testDB;

/*Show List of Databases with their Create Date */

SELECT name, database_id, create_date 

FROM sys.databases ; 

 

 

DROP DATABASE

Be careful before using this operation because by deleting an existing database would result in loss of complete     information stored in the database. Make sure you have admin privilege before dropping any database.

/*Droping the Database*/

 

DROP DATABASE testDB;

 

CREATE TABLE

Creating a basic table involves naming the table and defining its columns and each column's data type.The SQL     CREATE TABLE statement is used to create a new table.

 

USE [mwd]

GO

 

/****** Object:  Table [dbo].[Customers]    Script Date: 06/27/2016 11:04:49 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[Customers](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [Name] [varchar](max) NULL,

      [Age] [int] NULL,

      [Address] [nvarchar](max) NULL,

      [Salary] [float] NULL,

      [Postcode] [nvarchar](max) NULL,

      [Country] [varchar](50) NULL,

      [Continent] [varchar](50) NULL,

 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

INSERT INTO TABLE

The INSERT INTO statement is used to insert new records in a table.

/* Insert Query */

INSERT INTO Customers (Name,Age,Address,Salary,Postcode,Country,Continent)

VALUES ( 'Rose Teye', 25, 'Englnad', 4500.00, 'E6','UK','Europe' );

SELECT TABLE

The SELECT statement is used to select data from a database.The result is stored in a result table, called the result-set.

/*Select Query */

 SELECT ID, Name , Salary FROM Customers;

 SELECT *FROM Orders;

UPDATE TABLE

The UPDATE statement is used to update records in a table.

/*Update Query*/

 UPDATE Customers

 SET Name='Alfred', Address = 'Hamberg' WHERE Name='Hardik';

ALTER TABLE

The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You would also use ALTER TABLE command to add and drop various constraints on a an existing table.

/* Alter Table */

ALTER TABLE Customers ADD SEX char(10);

DROP COLUMN TABLE

The SQL DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.

/* Drop Column from Table*/

ALTER TABLE Customers DROP COLUMN SEX;

WHERE CLAUSE

The SQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. If the given condition is satisfied then only it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records. The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement.

  /* Where Clause */

 SELECT ID, Name, Salary FROM Customers WHERE Salary > 2000;

 

SELECT *FROM Customers WHERE Country = 'UK';

 

SELECT *FROM Customers WHERE ID = 1;

AND & OR OPERATOR

The SQL AND and OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQL statement.

/* AND & OR Operator */

 SELECT ID,Name,Salary FROM Customers WHERE Salary > 2000 AND  Age <25;

 

 SELECT *FROM Customers WHERE Country = 'India' AND Address = 'MP';

SELECT *FROM Customers WHERE Country = 'Mexico' OR Country ='UK';

 

SELECT *FROM Customers WHERE Country = 'Mexico' AND ( Salary = 1500 OR Salary = 2000);

 

TOP CLAUSE

The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.

/* Top Clause */

 SELECT TOP 3 * FROM Customers;

 SELECt TOP 5 * FROM Orders;

 SELECT TOP 50 PERCENT * FROM Customers;

 

LIKE & WILDCARDS OPERATOR

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. In SQL, wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table. A wildcard character can be used to substitute for any other character(s) in a string.

/* WildCards and Like Operators */

SELECT *FROM Customers WHERE SALARY LIKE '200%';

 

SELECT *FROM Customers WHERE Country LIKE '%di%';

SELECT *FROM Customers WHERE Country LIKE '[m-o]%';

 

SELECT *FROM Customers WHERE Country LIKE 'F_a__e';

 

 

SELECT *FROM Customers WHERE Country LIKE '__dia';

 

HANDLING DUPLICATES

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. The SQL DISTINCT keyword, which we already have discussed, is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.

/*Handling Duplicates */

 SELECT Salary FROM Customers ORDER BY Salary;

 SELECT DISTINCT Salary FROM Customers ORDER BY Salary;

 SELECT DISTINCT Country FROM Customers;

 

 

BETWEEN OPERATOR

The BETWEEN operator is used to select values within a range.

 

/* Between Operator */

 SELECT *FROM Orders WHERE AMOUNT  BETWEEN 1000 AND 2000;

SELECT *FROM Customers WHERE Name NOT BETWEEN 'K' AND 'R';

 

ALIAS IN SQL

You can rename a table or a column temporarily by giving another name known as alias. The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name does not change in the database. The column aliases are used to rename a table's columns for the purpose of a particular SQL query.

 

 

/* Alias Syntax */

/*table Alias*/

Select C.ID, C.Name, C.Age, O.Amount FROM Customers AS C, Orders AS O

WHERE C.ID = O.CustomerID;

 

/*Coloumn Alias*/

SELECT ID AS Customer_ID, Name AS Customer_Name FROM Customers

WHERE Salary IS NOT NULL;