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 JOIN
SQL VIEWS
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)
OLAP in SQL
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
Output:
test
mysql
phpmyadmin
performance schema
information_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')
Output:
#13. Select ALL from a Table (See all information of a Table)
Select * From students
Comment:
* 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'
Output:
#15. Update Table with Where Clause
Update students SET name = 'Alisa', Gender = 'Female', Faculty = 'Agriculture', dept = 'Agriculture' Where student_id = '2';
Output:
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
Output:
We are working to complete the post. Keep patience. Thanks