SQL Commands

By | August 25, 2019

SQL is a very powerful and easy to use Database Management System, which helps the developer to create and manage Database. There are many commands present in SQL, these commands usually termed as queries.

In tech-related interviews, you might face the direct questions related to SQL, or they can ask you to write a query to perform an action on the database. Here in this article, we have provided the top SQL commands.

Basic SQL Commands

Here is a manifest Database on which our all Queries will be performed.

Table Name customers

Id FirstName LastName Mobile
1 Will Drove 7878787878
2 David Kin 1212121212
3 John Adams 2323232323
4 Will Smith 3434343434
5 James Robert 4545454545
6 Mark Uzumaki 5656565656
7 Sofi Ketchum 6767676767

Table Name employee

Id FirstName LastName Salary
1 Will Drove 100000
2 David Kin 90000
3 John Adams 300000
4 Will Smith 500000
5 James Robert 20000
6 Mark Uzumaki 400000
7 Sofi Ketchum 210000

Write a command to show all the table present in the selected database

SHOW TABLES;
customers
employee

Write a Query to display the information about the columns of the Customers table.

SHOW COLUMNS FROM customers;

Field Type Null Key
ID Int(11) No PRI
FirstName varchar(20) No
LastName Varchar(30) No
Mobile int(20) NO

Write a Query to display only the first name of all the customers

SELECT FirstName FROM customers;
FirstName
Will
David
John
Will
James
Mark
Sofi

Write a query to display the first and the last name of all the customers.

SELECT FirstName, LastName
FROM customers;
FirstName LastName
Will Drove
David Kin
John Adams
Will Smith
James Robert
Mark Uzumaki
Sofi Ketchum

Write a Query to display the complete customer table.

SELECT *FROM customers;

Write a SQL command that eliminates the duplicate record and only unique ones.

SELECT DISTINCT FirstName
FROM customers;
FirstName
Will
David
John
James
Mark
Sofi

Write a query to display the first 2 records of the customer’s table.

SELECT *
FROM customers LIMIT 2;
Id FirstName LastName Mobile
1 Will Drove 7878787878
2 David Kin 1212121212

Write a SQL query to sort the customer’s tables by their first name;

SELECT * FROM customer
ORDER BY FirstName;
Id FirstName LastName Mobile
2 David Kin 1212121212
5 James Robert 4545454545
3 John Adams 2323232323
6 Mark Uzumaki 5656565656
7 Sofi Ketchum 6767676767
1 Will Drove 7878787878
4 Will Smith 3434343434

Write a query to select the customer with id 3.

SELECT * FROM customers
WHERE ID=3;
Id FirstName LastName Mobile
3 John Adams 2323232323

Write a query to select and display all those customers with an id greater than 3.

SELECT * FROM customers
WHERE ID > 3;

Write a query to select those customers which id number is between 2 to 4 (include):

SELECT * FROM customers
WHERE ID BETWEEN 2 AND 4;
Id FirstName LastName Mobile
2 David Kin 1212121212
3 John Adams 2323232323
4 Will Smith 3434343434

Write a query to select those customers whose name is will.

SELECT *FROM customers
WHERE FirstName = 'Will';
Id FirstName LastName Mobile
1 Will Drove 7878787878
4 Will Smith 3434343434

Write a query to select those customers whose id number is greater than 1 and less than 4.

SELECT *
FROM customers
WHERE Id > 1 AND Id <= 4;
2 David Kin 1212121212
3 John Adams 2323232323

Write a query to join the FirstName and the LastName with ‘-‘.

SELECT CONCAT(FirstName, '- ' , LastName) FROM customers;
CONCAT(FirstName, ‘- ‘ , LastName)
Will-Drove
David-Kin
John-Adams
Will-Smith
James-Robert
Mark-Uzumaki
Sofi-Ketchum

Write a query to display all the LastName from the table customers in uppercase.

SELECT UPPER(FirstName) AS FIRST
FROM employees;
FIRST
WILL
DAVID
JOHN
WILL
JAMES
MARK
SOFI

Write a query to display the average salary of all employees from Employee table.

SELECT AVG(Salary) FROM employee;

Write a query to select those employees whose First Name starts with J from.

SELECT * FROM employee
WHERE FirstName LIKE 'J%'; 

Write a query to select those employees whose Last Name starts with h from.

SELECT * FROM employee
WHERE FirstName LIKE '%h';

Write a query to select the minimum salary from the employee Table.

SELECT MIN(Salary) AS SALARY FROM employee;

Write a query to remove a row from the table employee where FirstName is will.

DELETE FROM employee
WHERE FirstName = ‘will’;

Write a query to select those customers which id is equal to employee id:

SELECT *
FROM customers, employee
WHERE customers.ID=employee.ID
ORDER BY customers.ID;

Write a Query to create a View for Firstname and Salary of Employee table

CREATE VIEW Employee_Sal AS
SELECT FirstName, Salary
FROM Employee;

Write a Query to select those employees which name contain 4 letters and the end letter is y

Select * from employee where FirstName like '___h';

Write a Query to select those employees whose salary is lies between 80000 to 500000.

Select * from employee where Salary between 80000 and 500000;

Write an SQL query to count of an employee having first name will.

SELECT COUNT(*) FROM employee
Where FirstName= 'Will';

Write an SQL query to show those record which Customer table has but not Employee

SELECT * FROM Customers
MINUS
SELECT * FROM Employee;

Write a Query that shows the current date

SELECT CURDATE();

Write a query that shows current date as well as time/

SELECT NOW();

Write a Query to show the list of those employees having the same salary.

SELECT DISTINCT e1.id, e1.FirstName, e1.salary
FROM employee e1, employee e2
WHERE e1.Salary = e2.Salary
AND e1.id !=e2.id;

Write a query to select the second highest salary from the employee table

SELECT MAX(Salary) FROM employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee);

Write a Query to show all the common Information of Employee and Customers table

(SELECT * FROM Employee)
INTERSECT
(SELECT * FROM Customers);

Write a query to show only 50 % information of the entire customer table

SELECT * FROM
Customer
WHERE LIMIT= (SELECT count(customers.id)/2)

Leave a Reply

Your email address will not be published. Required fields are marked *