Primary, Intermediate and Advanced SQL 200 Queries with Examples

(We are working hard to complete this post)

1. Primary SQL

Show, Create, Rename, Alter, Truncate, Drop, Select, AS, IN, Distinct, Unique, Where, MAX, MIN, AVG, SUM, LIMIT, Update, Delete, Insert, Like, Between, Having, Ucase, Lcase, Round/Floor/Celling etc. 

2. Intermediate SQL

SQL Constraints
SQL Index
SQL Authorization (Grant and Revoke)
SQL Roles

3. Advanced SQL

Dynamic SQL
Embedded SQL
SQL Functions and Procedures
SQL Triggers
Recursion in SQL
SQL Aggregation Features (Ranking, windowing)

Learn Details with Queries and Examples
(SQL is not Case - sensitive, so capital or small letter meaning is same, you can use any format) 

#1. Show the Database Name

Show Databases

performance schema

#2. Create a Database 

Create Database database_name

Comment: Here, I created a database named "itjobexam". 

#3. Use a Database

Use database_name

Comment: Now I will use my database "itjobexam". I will create table.

#4. Drop a Database

Drop Database database_name

Comment: I will use my database "itjobexam". So I will not drop it at this moment. You can create a new database and try this query. 

#5. Create a Table named "students"

Create Table students(
student_id int(10) PRIMARY KEY AUTO_INCREMENT,
name varchar(100),
gender varchar(20), 
faculty varchar(20),
dept varchar(20)

Comment: A table named "students" will be created in your database.

#6. Show the Tables of my Database

Show Tables

Comment: you will see all table name of your database

#7. Show Table column names and full structure

Show columns from students

Comment: you will see all column name of students table.

#8. Rename a Table

Rename Table students TO student_info

Comment: We do not need to change our "students" table name at this moment. By above query, your table name will be changed to student_info from students. 

#9. Alter a Table

Add a Column: 

Alter Table students ADD address varchar(255)

Change a column name

Alter Table students CHANGE address email varchar(100)

Drop a Column name

Alter Table students DROP email

Comment: Try yourself. I do not need this at this moment. 

#10. Truncate a Table (Truncate means delete all information, but not Table structure)

Truncate Table students

Comment: We have not any info yet in our  table.

#11. DROP a Table (Drop means delete the table absolutely with name and structure)

DROP Table students

Comment: If you do this, your table "students" will be deleted completely. I will not do this at this moment. 

#12. Insert Into Table

Insert Into students(name, gender, faculty, dept) VALUES('Mamun', 'Male', 'CSE', 'CSE')

Comment: student_id will be generated automatically because it is auto incremented and mamun, male, CSE, CSE this info will be inserted in the table. 

Adding some more data in the table:

 Insert Into students(name, gender, faculty, dept) 
 VALUES  ('Mamun', 'Male', 'CSE', 'CSE'),
                  ('Sojib', 'Male', 'CSE', 'CSE'),
                  ('Hasib', 'Male', 'CSE', 'EEE'),
                  ('Yousuf', 'Male', 'Arts', 'Political Science'),
                  ('Alamin', 'Male', 'Commerce', 'BBA')


#13. Select ALL from a Table (See all information of a Table)

Select * From students

* means All. * Denotes to retrieve all column's information from a table, you can use it by a specific column name too like below.

Select student_id, name From students

It will shows only student_id and name column in your output. 

#14. SQL WHERE Clause (WHERE is used to set condition)

Select * From students Where dept = 'CSE'


#15. Update Table with Where Clause

Update students SET name = 'Alisa', Gender = 'Female', Faculty = 'Agriculture', dept = 'Agriculture' Where student_id = '2';  


Comment: If you miss to write where clause here, It will change all rows of your table by your new value. So be careful when using where clause.

#16. SELECT with Aliasing (Aliasing means User defined Output Table Column Name)

Select student_id AS Roll, name, dept From students 


We are working to complete the post. Keep patience. Thanks

أحدث أقدم