FIFA-2022 Career Guide Free Tutorials Go to Your University Placement Preparation 
0 like 0 dislike
in Database Management System (DBMS) by (130 points)
What are the commonly asked MySQL Queries?

Different queries for database creation, table creation, manipulation, and retrieving the information from a database with the help of runnable examples and snapshots.

1 Answer

0 like 0 dislike
by (130 points)
Best answer


What is a query?

A query is a command/statement used to fetch some specified data from tables of any particular database.

1. Query to create a database:

    For this, we need to follow the syntax-

    create database database_name;

As you press enter key a database of given name will be created. For example, we have to create a database naming db;

create database db; 

create database query

Hence, the database is created.

2. Query to use the database:

    To use the database just created, you need to issue a command-

     use db;

use database query

As the above query is executed, a statement is shown in result 'Database changed' which means that database is currently in use and further changes will be reflected in this database only.

3. Create a table:

We are creating a table naming T1 having columns ID, FirstName, LastName, Age and Salary and the datatypes are integer for numbers, char for alphabets and decimal for salary figures along with data size respectively.

The syntax to create a table is as follows-

create table T1(ID integer(10),FirstName char(20),LastName char(20),Age integer(10),Salary decimal);

create table query

Hence, the table is created and you can check by using following command:

show tables;

show table in database query

So, the database db has only one table i.e., T1.

4. Inserting data into table:

To insert data we need to execute the following syntax-

insert into T1 values(101,"Aman","Sharma",26,25000);

-Always remember to put char values inside "  ".

 insert value into table query

Hence, the value is inserted in the table. Similarly, you can insert several other values into table.

insert value into table query 

5. Display data from table:

To display the data we use "select" command and it retrieves everything stored from a table. We need to specify asterisk in the select list-

select * from T1;

display table data query

All the data entered is displayed. 

6. Rename table:

To rename the existing table you need to execute the query mentioned below-

rename table t1 to employeeinfo;

rename table query

Hence, the existing table T1 is renamed as employeeinfo. 

 7. Retrieve particular rows:

To retrieve particular rows from table based on some condition we use clauses. 

What is a clause?

Any well-defined parts of SQL statements having specific condition is called a clause.

Types of SQL Clauses-

  • WHERE CLAUSE- It is used to select specific rows.

For example, You can select particular rows from a table by specifying condition through where clause of the select statement.

select *from employeeinfo

where age=24;

where clause query

  • ORDER BY CLAUSE- It is used to sort the results of a query.

For example, to display the list of employees in the alphabetical order of their FirstName, we can use this command-

select *from employeeinfo 

order by FirstName;

order by clause query

We can see that the FirstName of all employees is arranged alphabetically.

  • GROUP BY CLAUSE- It is used to group the results of data in terms of count function of a query. Now, execute the following query-

select FirstName, count(*)

from employeeinfo 

group by age;

group by clause query

We can see that two same ages are grouped in front of a single employee name.

  • DISTINCT CLAUSE- The "distinct" keyword eliminates the duplicate rows from the results of a select statement. For example-

select distinct age from employeeinfo;

distinct query

Now, you can see that it eliminates one entry of age having a similar value i.e., 24.

8. Query to describe a table:

Once you have created a table, you can view its structure by using DESCRIBE command of MySQL or in short DESC.

DESC employeeinfo;

table description query

9. Query to delete table data: If you want to delete the data of table without deleting its structure then you have to use "truncate" command of SQL. After executing the truncate command if you run the select query then it will show you empty set as all the data of existing table has been deleted.

truncate table employeeinfo;

query to delete table data

10. Query to delete the table: To delete the structure of the table we use "DROP" command. After we delete the table, we can check by using show tables command in the current database which will show empty set as the table is already deleted.

To delete any existing table use the code-

drop table employeeinfo;

delete entire table

11. Query to delete the database: Lastly, if we want to delete the entire database with all its existing tables and constraints permanently, we can use "DROP" database command. If we run "show databases" command after that then the list of databases won't include the name of deleted database.

drop database db;

query to delete a database

show databases;

to check the database deleted or not

We can clearly see that the list of databases have no database naming db as it has been already deleted from the system.

All these queries together comprise of basic queries of MySQL.

Learn & Improve In-Demand Data Skills Online in this Summer With  These High Quality Courses[Recommended by GOEDUHUB]:-

Best Data Science Online Courses[Lists] on:-

Claim your 10 Days FREE Trial for Pluralsight.

Best Data Science Courses on Datacamp
Best Data Science Courses on Coursera
Best Data Science Courses on Udemy
Best Data Science Courses on Pluralsight
Best Data Science Courses & Microdegrees on Udacity
Best Artificial Intelligence[AI] Courses on Coursera
Best Machine Learning[ML] Courses on Coursera
Best Python Programming Courses on Coursera
Best Artificial Intelligence[AI] Courses on Udemy
Best Python Programming Courses on Udemy

Related questions

0 like 0 dislike
1 answer 448 views
asked Sep 14, 2020 in Coding Questions by Vaibhav98 Goeduhub's Expert (2.3k points)

 Important Lists:

Important Lists, Exams & Cutoffs Exams after Graduation PSUs


About Us | Contact Us || Terms & Conditions | Privacy Policy ||  Youtube Channel || Telegram Channel © Social::   |  | 


Free Online Directory