MySQL queries for Practical File Informatics Practices Class 11

[1] Create Database

create database class11;
[2] Open database
use class11;
[3] To create a student table with the student id, class, section, gender, name, dob, and marks as
attributes where the student id is the primary key.
create table student
(studentid int(4) primary key,
class char(2),
section char(1),
gender char(1),
name varchar(20),
dob date,
marks decimal(5,2));
[4] View the structure of the table
desc student; OR
describe student
[5] To insert the details of at least 10 students in the above table.
insert into student values
(1101,'XI','A','M','Aksh','2005/12/23',88.21),
(1102,'XI','B','F','Moksha','2005/03/24',77.90),
(1103,'XI','A','F','Archi','2006/04/21',76.20),
(1104,'XI','B','M','Bhavin','2005/09/15',68.23),
(1105,'XI','C','M','Kevin','2005/08/23',66.33),
(1106,'XI','C','F','Naadiya','2005/10/27',62.33),
(1107,'XI','D','M','Krish','2005/01/23',84.33),
(1108,'XI','D','M','Ayush','2005/04/23',55.33),
(1109,'XI','C','F','Shruti','2005/06/01',74.33),
(1110,'XI','D','F','Shivi','2005/10/19',72.30);
[6] Display the details of the student table.
select * from student;
[7] To delete the details of a particular student in the above table.
Delete record of students who secured less than 65 marks.
delete from student where marks <65;
[8] To increase marks by 5% for those students who have studentid more than 1105.
update stduent set marks=makrs+(marks*0.05) where studentid>1105;
[9] To display the content of the table of female students.
select * from student where gender = 'f';
[10] To display studentid, Name and Marks of those students who are scoring marks more than 50.
select studentid,name,marks from student where marks>50;
[11] To find the average of marks from the student table.
select avg(marks) from student;
[12] To find the number of students, who are from section ‘A’.
select count(*) from student where section = 'A';
[13] To add a new column email in the above table with the appropriate data type.
alter table student add column email varchar(20);
[14] To add the email ids of each student in the previously created email column.
update student set email='[email protected]';
[15] To display the information of all the students, whose name contains ‘sh’
select * from student where name like 'sh%';
[16] To display the information of all the students, whose name starts with ‘sh’
select * from stduent where name like 'sh%';
[17] To display studentid, Name, DOB of those students who are born between ‘2005- 01-01’ and
‘2005-12-31’.
select studentif, name, dob from student where dob between '2005-01-01' and '2005-12-31';
[18] To display studentid, Name, DOB, Marks, Email of those male students in ascending order of
their
names.
select studentid, name, dob from student order by name;
[19] To display stduentid, Gender, Name, DOB, Marks, Email in descending order of their marks.
select studentid, gender, name, dob, marks, email from student order by marks desc;
[20] To display the unique section available in the table.
select distnict section from student;
GROUP BY

The GROUP BY clause is used to arrange the rows in a group using a particular column value. If there
are multiple rows with the same value for a column then all those rows will be grouped with that column
value. It is used with the SELECT statement and is placed after the WHERE clause and before the
ORDER BY if used any.

Syntax:

SELECT column1,column2,…
FROM table_name
WHERE condition
GROUP BY column1,column2, …
Order BY column1, column2, ….

The GROUP BY clause is generally used with aggregate functions like SUM, AVG, COUNT, MAX,
MIN. The aggregate functions provide information about each group.

1. GROUP BY clause without aggregate functions

cricketers

name age country

Virat 33 India

Rohit 34 India

ABD 37 South Africa

Williamson 31 New Zealand

Stokes 30 England

Boult 32 New Zealand

Finch 35 Australia

Warner 35 Australia

For example:

SELECT country
FROM cricketers
GROUP BY country;

Output:
country

India

South Africa

New Zealand

England

Australia

In the above example, the country is selected and grouped according to the country which ensures
unique occurrences of each country.

2. GROUP BY clause with aggregate functions

For example:

SELECT COUNT(name) AS number_of_players, country

FROM cricketers
GROUP BY country;

Output:

number_of_players country

2 India

1 South Africa

2 New Zealand

1 England

2 Australia

Similarly, when GROUP BY is used with multiple columns, all those rows will be grouped which have
the same value for all the columns used for grouping.

HAVING

The HAVING clause is used with the GROUP BY clause in a query to specify come conditions and
filter some groups or aggregates that fulfill those conditions. The difference between WHERE and
HAVING is, WHERE is used to filter rows, and HAVING is used to filter groups by applying some
conditions.

Syntax:
SELECT column1, column2, …
FROM table_name
WHERE conditions
GROUP BY column1, column2, …..
HAVING conditions

For example:

cricketers

name age country

Virat 33 India

Rohit 34 India

ABD 37 South Africa

Williamson 31 New Zealand

Stokes 30 England

Boult 32 New Zealand

Finch 35 Australia

Warner 35 Australia

SELECT COUNT(name) AS number_of_players, country

FROM cricketers
GROUP BY country
HAVING number_of_players>1;

Output:

number_of_players country

2 India

2 New Zealand

2 Australia

In the above example, the players are grouped according to their country and those countries which have
the number of players >1 are selected using the HAVING clause

GROUP BY

The GROUP BY clause is used to arrange the rows in a group using a particular column value. If there
are multiple rows with the same value for a column then all those rows will be grouped with that column
value. It is used with the SELECT statement and is placed after the WHERE clause and before the
ORDER BY if used any.

Syntax:

SELECT column1,column2,…
FROM table_name
WHERE condition
GROUP BY column1,column2, …
Order BY column1, column2, ….

The GROUP BY clause is generally used with aggregate functions like SUM, AVG, COUNT, MAX,
MIN. The aggregate functions provide information about each group.

1. GROUP BY clause without aggregate functions

cricketers

name age country

Virat 33 India

Rohit 34 India

ABD 37 South Africa

Williamson 31 New Zealand

Stokes 30 England

Boult 32 New Zealand

Finch 35 Australia

Warner 35 Australia

For example:

SELECT country
FROM cricketers
GROUP BY country;

Output:

country

India

South Africa

New Zealand
country

England

Australia

In the above example, the country is selected and grouped according to the country which ensures
unique occurrences of each country.

2. GROUP BY clause with aggregate functions

For example:

SELECT COUNT(name) AS number_of_players, country

FROM cricketers
GROUP BY country;

Output:

number_of_players country

2 India

1 South Africa

2 New Zealand

1 England

2 Australia

Similarly, when GROUP BY is used with multiple columns, all those rows will be grouped which have
the same value for all the columns used for grouping.

HAVING

The HAVING clause is used with the GROUP BY clause in a query to specify come conditions and
filter some groups or aggregates that fulfill those conditions. The difference between WHERE and
HAVING is, WHERE is used to filter rows, and HAVING is used to filter groups by applying some
conditions.

Syntax:

SELECT column1, column2, …

FROM table_name
WHERE conditions
GROUP BY column1, column2, …..
HAVING conditions
For example:

cricketers

name age country

Virat 33 India

Rohit 34 India

ABD 37 South Africa

Williamson 31 New Zealand

Stokes 30 England

Boult 32 New Zealand

Finch 35 Australia

Warner 35 Australia

SELECT COUNT(name) AS number_of_players, country

FROM cricketers
GROUP BY country
HAVING number_of_players>1;

Output:

number_of_players country

2 India

2 New Zealand

2 Australia

In the above example, the players are grouped according to their country and those countries which have
the number of players >1 are selected using the HAVING clause
. SELECT Statement

The SELECT statement in SQL is used to retrieve data from one or more tables in a database. It is a
fundamental and powerful command that allows you to query and display information stored in a
relational database.

Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;

Example:

1. SELECT * FROM employees; (* gives you the complete data of the table).

2. SELECT first_name, last_name FROM employees; (gives you all the data of the first name and last
name from the table employees).

2. SELECT DISTINCT

The SELECT DISTINCT statement is used to retrieve unique values from a specific column in a table. It
eliminates duplicate rows and returns only distinct values.

Syntax: SELECT DISTINCT column1, column2, ... FROM table_name WHERE condition;

Example:

1. SELECT DISTINCT first_name, last_name FROM students; (gives you the unique values of the first
name and last name from the table students).

3. WHERE Clause

The WHERE clause in SQL is used to filter the rows returned by a SELECT statement based on a specified
condition. It allows you to retrieve only the rows that meet certain criteria.

Syntax: SELECT column1, column2, … FROM table_name WHERE condition;

Example:

1. SELECT * FROM students WHERE age > 20; (gives you the data of students whose age is above
20).

4. ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result set of a SELECT statement based on one or more
columns. It allows you to specify the order in which the rows should be displayed, either in ascending
(default) or descending order.

Syntax: SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC | DESC],
column2 [ASC | DESC], ...;

Example:
1. SELECT * FROM students ORDER BY age; (organizes the data of the table by age in
ascending/descending order).

5. LIMIT and OFFSET

The LIMIT and OFFSET clauses in SQL are used to control the number of rows returned by a SELECT
statement, allowing you to retrieve a specific subset of the result set. These clauses are often used
together to implement pagination or to fetch a limited number of rows starting from a certain position.

Syntax: SELECT column1, column2, … FROM table_name LIMIT number_of_rows OFFSET

offset_value;

Example:

1. SELECT * FROM students LIMIT 2 OFFSET 2; (the LIMIT 2 restricts the result set to two rows, and
the OFFSET 2 skips the first two rows.)

6. INSERT Statement

The INSERT statement in SQL is used to insert new records (rows) into a table. It adds data to an
existing table by specifying the values to be inserted into each column.

Syntax: INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Example:

1. INSERT INTO students (student_id, first_name, last_name, age, grade) VALUES (7, ‘Alice’,
‘Johnson’, 21, ‘B’); (adds a new student to the students’ table with the required details).

7. UPDATE Statement

The UPDATE statement in SQL is used to modify existing records (rows) in a table. It allows you to
change the values of one or more columns in the specified rows based on a specified condition.

Syntax: UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

Example:

1. UPDATE students SET age = 20, grade = ‘B’ WHERE student_id = 3; (update the students’ table
with the student’s age as 20, grade as B where id is 3).

8. DELETE Statement

The DELETE statement in SQL is used to remove records (rows) from a table based on a specified
condition. It permanently removes data from the table.

Syntax: DELETE FROM table_name WHERE condition;

Example:
1. DELETE FROM students WHERE student_id = 4; (deletes the entire data of student whose id is 4
from the table students).

9. COUNT() Function

The COUNT function in SQL is used to count the number of rows in a table or the number of rows that
satisfy a particular condition in a specified column.

Syntax: SELECT COUNT(*) FROM table_name WHERE condition;

Example:

1. SELECT COUNT(*) FROM students; (displays the count of total number of students from the table
students).

10. SUM() Function

The SUM function in SQL is used to calculate the sum of values in a numeric column, typically within a
specified group or for all rows in a table.

Syntax: SELECT SUM(column_name) FROM table_name WHERE condition;

Example:

1. SELECT SUM(amount) FROM sales; (displays the total amount from the sales table).

11. GROUP BY Clause

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into
summary rows, like “total sales per product” or “average score per student.” It’s often used with
aggregate functions like SUM, COUNT, AVG, etc.

Syntax: SELECT column1, column2, …, aggregate_function(column) FROM table_name WHERE

condition GROUP BY column1, column2, …;

Example:

1. SELECT product, SUM(amount) as total_sales FROM sales GROUP BY product; (groups the data
by the “product” column and displays the result.)

12. HAVING Clause

The HAVING clause in SQL is used in conjunction with the GROUP BY clause to filter the results of a
grouped query. While the WHERE clause filters rows before grouping, the HAVING clause filters groups
after the grouping has taken place. It is commonly used with aggregate functions like SUM, COUNT, AVG,
etc.

Syntax: SELECT column1, column2, …, aggregate_function(column) FROM table_name WHERE

condition GROUP BY column1, column2, … HAVING condition;
Example:

1. SELECT product, SUM(amount) as total_sales FROM sales GROUP BY product HAVING

total_sales > 1000; (it initially sums up the sales amount and groups the data and then displays only that
data which is greater than 1000).

13. JOIN Statements

In SQL, the JOIN statement is used to combine rows from two or more tables based on a related column
between them.

There are different types of joins, including INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT
JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

Syntax: SELECT columns FROM table1 JOIN table2 ON table1.column = table2.column;

Example:

1. SELECT students.first_name, students.last_name, courses.course_name FROM students JOIN

courses ON students.student_id = courses.course_id; (it combines rows from the “students” and
“courses” tables based on the relationship defined in the ON clause.)

14. Subqueries

A subquery, also known as a nested query or inner query, is a query embedded within another query. It
can be used to retrieve data that will be used in the main query as a condition to further restrict the data
to be retrieved. Subqueries can be used in various parts of a SQL statement, including the SELECT,
FROM, WHERE, and HAVING clauses.

Syntax: SELECT column1, column2, … FROM table_name WHERE column_name operator (SELECT
column_name FROM another_table WHERE condition);

Example:

1. SELECT first_name FROM students WHERE student_id IN (SELECT student_id FROM grades
WHERE grade = ‘A’); (it finds the student_id values from the “grades” table where the grade is ‘A’.
The main query then selects the first_name of students whose student_id is in the result set of the
subquery.)

15. Views

A view is a virtual table based on the result of a SELECT query. It allows you to simplify complex
queries, encapsulate logic, and present a subset or transformation of the data to users without exposing
the underlying table structure.

Views are often used to enhance data security by restricting direct access to certain tables and providing
a controlled way to access specific data.

Syntax: CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE
condition;
Example:

1. CREATE VIEW high_performers AS SELECT student_id, first_name, last_name, grade FROM

students WHERE grade = ‘A’;
MySQL online editor

Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for
free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the
OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code
when you choose language as 'MySQL' and start writing queries to learn and test online without
worrying about tedious process of installation.

About MySQL

MySQL is a open-source, free and very popular relational database management system which is
developed, distributed and supported by Oracle corporation.

Key Features:

 Open-source relational database management systems.

 Reliable, very fast and easy to use database server.
 Works on client-server model.
 Highly Secure and Scalable
 High Performance
 High productivity as it uses stored procedures, triggers, views to write a highly productive code.
 Supports large databases efficiently.
 Supports many operating systems like Linux*,CentOS*, Solaris*,Ubuntu*,Windows*, MacOS*,FreeBSD*
and others.

Syntax help

Commands
CREATE TABLE STUDENT(
REG_NO CHAR(5),
SNAME VARCHAR(15),
AGE INTEGER(2),
DEPT VARCHAR(10),
CLASS CHAR(3)
);
-- VIEW TABLE STRUCTURE
DESC STUDENT;
-- INSERTING DATA INTO TABLE
INSERT INTO STUDENT
VALUES('M1001','HARISH',19,'ME','ME1');
INSERT INTO STUDENT
VALUES('M1002','AKASH',20,'ME','ME2');
INSERT INTO STUDENT
VALUES('C1001','SNEHA',20,'CSE','CS1');
INSERT INTO STUDENT
VALUES('C1002','LITHYA',19,'CSE','CS2');
INSERT INTO STUDENT
VALUES('E1001','RAVI',20,'ECE','EC1');
INSERT INTO STUDENT
VALUES('E1002','LEENA',21,'EEE','EE1');
INSERT INTO STUDENT
VALUES('E1003','ROSE',20,'ECE','EC2');
-- VIEW ALL RECORDS:
SELECT*FROM STUDENT;
-- OTHER QURIES:
-- LISTING THE STUDENTS WHOSE DEPART MENT IS
CSE:
SELECT*FROM STUDENT WHERE DEPT='CSE';
-- LIST ALL THE STUDENTS WHERE AGE 20 AND MORE
IN ME DEPARTMENT
SELECT*FROM STUDENT WHERE AGE>=20 AND
DEPT='ME';
-- LIST THE STUDENTS DEPARTMENT WISE:
SELECT*FROM STUDENT GROUP BY DEPT ORDER BY
SNAME;
-- MODIFY THE CLASS ME2 TO ME1:
UPDATE STUDENT SET CLASS='ME1' WHERE
CLASS='ME2';
SELECT*FROM STUDENT;
-- CHECK THE UNIQUENESS OF REGISTER NO:
SELECT DISTINCT REG_NO FROM STUDENT;
1. CREATE
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);

Example
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);

2. ALTER
ALTER TABLE Table_name ADD column_name datatype;

Example
INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');

3. TRUNCATE
TRUNCATE table table_name;

4. DROP
DROP TABLE table_name;
5. RENAME
RENAME TABLE table_name1 to new_table_name1;

6. COMMENT

Single-Line Comments:
--Line1;

Multi-Line comments:
/* Line1,
Line2 */

DML Commands
1. INSERT
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2,
value3, ...);

Note: Column names are optional.

Example
INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');

2. SELECT
SELECT column1, column2, ...
FROM table_name
[where condition];

Example
SELECT * FROM EMPLOYEE where dept ='sales';

3. UPDATE
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';

4. DELETE
DELETE FROM table_name where condition;

Example
DELETE from EMPLOYEE where empId='0001';

Indexes
1. CREATE INDEX
CREATE INDEX index_name on table_name(column_name);

 To Create Unique index:

CREATE UNIQUE INDEX index_name on table_name(column_name);

2. DROP INDEX
DROP INDEX index_name ON table_name;

Views
1. Create a View
Creating a View:
CREATE VIEW View_name AS
Query;

2. How to call view

SELECT * FROM View_name;

3. Altering a View
ALTER View View_name AS
Query;

4. Deleting a View
DROP VIEW View_name;

Triggers
1. Create a Trigger
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */

2. Drop a Trigger
DROP TRIGGER [IF EXISTS] trigger_name;

Stored Procedures
1. Create a Stored Procedure
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;

2. How to call Stored procedure

CALL sp_name;

3. How to delete stored procedure

DROP PROCEDURE sp_name;

Joins
1. INNER JOIN
SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;
2. LEFT JOIN
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;

3. RIGHT JOIN
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;

4. CROSS JOIN
SELECT select_list from TABLE1 CROSS JOIN TABLE2;
The data-driven world of today requires a lot of data to function. A majority of the data is stored as
databases, which is an organized collection of data. A database management system or DBMS in short
acts as a bridge between the database and end-user to ensure that the data is well organized and easily
accessible. A DBMS assists in optimizing, storing, retrieving, and managing data in databases.

MySQL is the most widely used database management system software for managing relational
databases today. It is an open-source database that is supported by the Oracle Corporation. When
compared to Microsoft SQL Server and Oracle Database, it is a quick, scalable, and simple to use
database management system. It’s frequently combined with backend languages to build powerful and
dynamic server-side or web-based enterprise applications. MySQL AB, a Swedish business, created,
marketed, and supported it, and it was written in the C and C++ programming languages. Many small
and large businesses use MySQL. It supports a variety of operating systems, including Windows, Linux,
macOS, and others. MySQL is a Relational Database Management System (RDBMS) that offers a
variety of features, including:

 It allows us to use tables, rows, columns, and indexes and to perform database operations on them.
 Tables (collection of rows and columns), also known as relations, are used to construct database
relationships.
 It ensures that the Referential Integrity (referential integrity is an RDBMS concept which states that any
foreign key field must agree with the primary key that is referenced by the foreign key) between rows or
columns of different tables is maintained.
 It automatically updates the table indexes as soon as a record is changed.
 It employs a number of SQL queries and integrates useful data from many columns and rows for end-
users.

This article covers all the most frequently used commands and statements. With this handy collection of
MySQL commands, you’ll be able to operate more efficiently and effectively with your MySQL
databases. You’ll see at a glance the most commonly used commands and statements you need for
creating and managing the databases in MySQL.

Confused about your next job?

In 4 simple steps you can find your personalised career roadmap in Software development for FREE

Expand in New Tab

Table Of Contents

show

 MySQL commands
o Working with databases
o Working with tables
o Working with indexes
o Working with views
o Working with stored procedures
o Querying data from tables
o Join
Inner Join

Left join

o Modifying data in tables
o Searching
o MySQL command-line client commands
 Conclusion
 Frequently Asked Questions
 Additional Resources

MySQL commands
Working with databases

A database is a collection of organized records that the user can conveniently access and manage. It
organizes the data into tables, rows, columns, and indexes to make it easier to retrieve the information
we need quickly. Let’s try to build a student database using the following command.

The following syntax can create a database. It also verifies whether the database name is already in
use.

CREATE DATABASE IF NOT EXISTS db_name;

Use the following syntax to replace the current database with another database you’re working on:

mysql> USE db_name;

Using the following syntax, we can permanently destroy a database and its associated files. All the
tables that are a part of that database will be deleted if we use the following command.

DROP DATABASE IF EXISTS db_name;

Use the following syntax to display all databases on the current server:
mysql> SHOW DATABASES;
As you can see in the snapshot above, we created a database named ‘students’ using create command,
and deleted a database named class using DROP command.

Working with tables

A table in a database is a collection of related data organized in a table structure. It’s made up of
columns and rows. A table is a collection of data components in relational and flat-file databases that
uses a model of vertical columns and horizontal rows, with the cell being the unit where a row and
column intersect.

Let’s create the below table:

MARKS TABLE

ID Name Marks

001 Ashish 94

002 Bharat 81

003 Deepak 78

004 Fatima 92

This command displays all tables in a current database.

SHOW TABLES;

This command creates a new table:

CREATE TABLE [IF NOT EXISTS] table_name(
Column_name1 datatype, Column_name2 datatype……);
The datatype can be integer, char (fixed length sring), varchar (variable length string), binary etc. The
data which is going to be stored in a column decides the datatype of the column. For example, if the
column is going to store numerals, integer datatype can be used or if the column name is going to store a
string of variable length, varchar can be used. For example, to create the above Marks table, type the
following code:
CREATE TABLE Marks(ID integer, Name varchar (100), Marks integer);

To insert values into a table type the following command:

INSERT INTO table_name
VALUES (value1, value2, value3, …);
The values should correspond to the column name in which the value is to be stored.
For example, to insert first column of the students table, you have to type the following command:
INSERT INTO Marks
VALUES (001, ‘Ashish’,94);

In a table, add a new column:

ALTER TABLE table
ADD column_name datatype;
For example to add Mentor’s Name column, you have to type the following command:
ALTER TABLE Marks
ADD Mentor varchar(100);

To remove a column from a table, do the following:

ALTER TABLE table_name
DROP column_name;

Add an index to a table on a column with a specified name:

ALTER TABLE table
ADD INDEX [name](column, …);

To add a primary key to a table, do the following

ALTER TABLE table_name
ADD PRIMARY KEY (column_name,…);

To remove the primary key of a table, do the following:

ALTER TABLE table_name
DROP PRIMARY KEY;

You can drop a table using the following command:

DROP TABLE [IF EXISTS] table_name;

To show the columns of a table, type the following code:

DESCRIBE table_name;

To show the information of a column of a table, type the following command:

DESCRIBE table_name column_name;
Working with indexes

Assume we have a contact book with the users’ names and phone numbers. We’re looking for a phone
number in this contact book. If the contact book is in an unordered manner, which means the names
aren’t organized alphabetically, we’ll have to go through all the pages and read every name until we
don’t locate the requested name. Sequential searching is the name for this form of search. While this
approach is correct, applying this in a large database will consume a lot of time. In this situation,
database indexing aids in the retrieval of the desired result and increases the query’s overall
performance.

Indexes are used to quickly locate records with certain column values. It is similar to the index given in
a book, you can easily locate a chapter using the index of the book.

To create an index with the specified name on a table:

CREATE INDEX index_name
ON table_name (column,…);
So, to create index of column Name of the above Marks table, we can write the following command:
CREATE INDEX new_index
ON Marks(Name);
Drop an index:
DROP INDEX index_name;
To drop the above created index, write the following command:
DROP INDEX new_index;

A unique index ensures that the index key includes no duplicate values, ensuring that each entry in the
table is distinct in some sense. To create a unique index, use the following syntax:
CREATE UNIQUE INDEX index_name
ON table_name (column,…);

Read more – Indexing in SQL

Working with views

A view is a database object that doesn’t have any data in it. Its contents are based on the table that serves
as the foundation. It has the same rows and columns as a genuine table. In MySQL, a View is a virtual
table that is produced by connecting one or more tables in a query. It works in the same way as the base
table, but it doesn’t have any data of its own. The fundamental distinction between a view and a table is
that views are definitions constructed on top of other tables (or views).

Create a new view by following these steps:

CREATE VIEW [IF NOT EXISTS] view_name
AS Select query ;

Let’s try to understand it with an example. We are going to create a view from the Marks table
mentioned above.
We can use SELECT to see the actual view, just like what we do with tables.

With the WITH CHECK OPTION, create a new view.

CREATE VIEW [IF NOT EXISTS] view_name
AS select_statement
WITH CHECK OPTION;

Make a new view or replace an existing one:

CREATE OR REPLACE view_name
AS
select_statement;

Consider this to drop a view:

DROP VIEW [IF EXISTS] view_name;
To drop the above view (new_view) created, we can use the command:
DROP VIEW new_view;

To drop multiple views:

DROP VIEW [IF EXISTS] view1, view2, …;

Rename a view:
RENAME TABLE view_name
TO new_view_name;

Show views from a database:

SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = ‘VIEW’;
Working with stored procedures

A stored procedure is a piece of prepared SQL code that you can save and reuse over and over. So, if
you have a SQL query that you create frequently, save it as a stored procedure and then call it to run it.
You can also pass parameters to a stored procedure so that it can act based on the value(s) of the
parameter(s) passed.In the normal computing language, it is a subroutine or a subprogram. MySQL
version 5 was the first to include it. It is currently supported by nearly all relational database systems.

Create a stored procedure, use syntax:

DELIMITER $$
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
body
END $$
DELIMITER;
To call a procedure in MySQL, use the syntax:
CALL procedure_name;

Let’s try to create a procedure. We will create a procedure that shows all the students who have scored
an A grade (90+ marks).

First, open MySQL workbench and select the database (students database in our case). Then we have to
store our procedure.

Now we can call the created procedure.

Drop a stored procedure:
DROP PROCEDURE [IF EXISTS] procedure_name;

Show stored procedures:

SHOW PROCEDURE STATUS
[LIKE ‘pattern’ | WHERE search_condition];

You can also create and store functions with parameters using the syntax:
DELIMITER $$
CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
— statements
END $$
DELIMITER ;

Drop a stored function:

DROP FUNCTION [IF EXISTS] function_name;

Querying data from tables

A query is any command used to retrieve data from a table in relational database management systems.
The SELECT statement is most often used in Structured Query Language (SQL) queries. Select is a type
of DQL (Data Query Language) command. DQL statements are used to perform queries.

For example, to query the entire contents of a table, use the following command:
SELECT * FROM table_name;
The above query will return each record of the given table.
For example:
If you want MySQL to show only some columns of the table, you can use the following syntax:
SELECT
column1, column2, …
FROM
Table_name;

Remove duplicate rows from a query’s result:

SELECT
DISTINCT (column)
FROM
Table_name;
The above syntax will return distinct elements of the column specified. For example, if we have a
column ‘Country’ in a table, it will have a lot of duplicate values, i.e. there is a chance that more than
one person belongs to a country. If we want to see what are the distinct countries present in the table, we
can use distinct clause.

We can filter records using WHERE clause. It returns only those records which fulfill a specified set of
condition.
SELECT column_list
FROM table_name
WHERE condition;
For example, if we want to write a query to retrieve the name of students whose score is less than 80, it
will look something like this:
SELECT Name from Marks
WHERE Marks <80;

You can change the output of the column name using column alias:

SELECT
column1 AS alias_name,
expression AS alias,
…
FROM
Table_name;
The name of the column will be changed to alias_name in the result (not in the actual table).

Counting the number of rows in a table:

SELECT COUNT(*)
FROM table_name;

You can sort a result set in ascending and descending order by writing the following code::
SELECT
select_list
FROM
table_name
ORDER BY
column1 ASC [DESC],
column2 ASC [DESC];

Group rows using the GROUP BY clause.

SELECT select_list
FROM table_name
GROUP BY column_1, column_2, …;

Join

In a join, you can get records from two (or more) logically connected tables in one result set. JOIN
clauses are used to return the rows of two or more queries that use two or more tables that have a
meaningful relationship based on a set of values in common. These values are normally the same
column name and datatype that occur in both of the connected tables. The join key is usually, but not
always, the primary key of one table and a foreign key in another table. As long as the data in the
columns matches, the join can be executed.

Let’s look at the following tables to understand join better:

members_id name

1 John

2 Jane

3 Mary

4 David

5 Amelia

committee_id name

1 John

2 Mary
3 Amelia

4 Joe

Inner Join

Each row from the first table is compared to every record from the second table in the inner join clause.
If the join condition is met in both rows, the inner join clause creates a new row with a column that
contains all columns from both tables and includes it in the result set. In other words, only matched rows
from both tables are included in the inner join clause. Each row from the first table is compared to every
record from the second table in the inner join clause.

To query data from multiple tables using inner join, use the following command:
SELECT select_list
FROM table1
INNER JOIN table2 ON condition;

Consider the following syntax to inner join the above tables:

SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee

FROM
members m
INNER JOIN committees c ON c.name = m.name;
It will produce the following result:

member_id member committee_id committee

1 John 1 John

3 Mary 2 Mary

5 Amelia 3 Amelia

Left join

A join predicate is required for a left join, just as it is for an inner join.The left join selects data from the
table on the left. The left join compares every row in the left table to every row in the right table. The
left join clause creates a new row whose columns comprise all columns of the rows in both tables if the
values in the two rows satisfy the join condition, and includes this row in the result set. The left join
clause still creates a new row whose columns contain columns from the left table and NULL for
columns from the right table if the values in the two rows do not match.

You can query data from multiple tables using left join by using the following syntax:
SELECT select_list
FROM table1
LEFT JOIN table2 ON condition;

In our case, if we type the following syntax in command line:

SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee

FROM
members m
LEFT JOIN committees c USING(name);
The resulting table will look like this:

members_id member committee_id committee

1 John 1 John

2 Jane NULL NULL

3 Mary 2 Mary

4 David NULL NULL

5 Amelia 3 Amelia

The right join clause is similar to the left join clause, with the exception that the left and right tables are
treated differently. Instead of selecting data from the left table, the right join selects data from the right
table.

You can query data from multiple tables using the right join:
SELECT select_list
FROM table1
RIGHT JOIN table2 ON condition;

For example, the following syntax right joins our table:

SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee

FROM
members m
RIGHT JOIN committees c on c.name = m.name;

mambers_id members committee_id committee

1 John 1 John

3 Mary 2 Mary

5 Amelia 3 Amelia

NULL NULL 4 Joe

A Cartesian product of rows from the joined tables is created by the cross join. To create the result set,
the cross join joins every row from the first table with every row from the right table. Let’s say there are
n rows in the first table and m rows in the second table. The nxm rows will be returned by the cross join
that joins the tables.

Syntax:
SELECT select_list
FROM table1
CROSS JOIN table2;

In order to cross join our two tables, the syntax will look like this:

SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee

FROM
members m
CROSS JOIN committees c;

Modifying data in tables

To add a new row to a table, do the following:

INSERT INTO table_name(column_list)
VALUES(value_list);

Create a table with several rows:

INSERT INTO table_name(column_list)
VALUES(value_list1),
(value_list2),
(value_list3),
…;

To update all rows in a table, do the following:

UPDATE table_name
SET column1 = value1,
…;
Update data for a set of rows defined by a WHERE clause criteria:
UPDATE table_name
SET column_1 = value_1,
…
WHERE condition

To delete all rows in a table, the syntax is:

DELETE FROM table_name;

To delete rows specified by a condition:

DELETE FROM table_name
WHERE condition;

Searching

LIKE and RLIKE clauses are used to search desired records from the table.

The SQL LIKE clause compares a value to other values that are similar using wildcard operators. With
the LIKE operator, there are two wildcards that can be used.

 The percentage sign (%)

 The underscore (_)

The % sign can be used to indicate zero, one, or more characters. A single number or letter is
represented by the underscore. These symbols can be mixed and matched.
The syntax for LIKE clause
SELECT select_list
FROM table_name
WHERE column LIKE ‘%pattern%’ (or ‘_ABC’);
E.g, ‘S%’ will fetch all values that start with S.
‘_AB’ will fetch all values that have A and B at second and third places respectively.

In MySQL, this operator is used to pattern match a string expression against a pattern.
Syntax:
SELECT select_list
FROM table_name
WHERE column RLIKE ‘regular_expression’;

MySQL command-line client commands

mysql -u [username] -p;

To connect MySQL server using MySQL command-line client with a username and password (MySQL
will prompt for a password).

mysql -u [username] -p [database];

To connect MySQL Server with a specified database using a username and password.

exit;
To exit MySQL command line.
mysql> system clear;
On Linux, type the above command to clear the MySQL screen console window.
On Windows OS, there is currently no command to clear the MySQL screen console.
SQL Commands: The Complete List (w/ Examples)

To get a data job, you are going to need to learn SQL. The common SQL commands and operators
discussed in this post are a great reference. If you're looking for more details and SQL resources, check
out our Complete guide to SQL.

Below is a comprehensive list of SQL commands, organized by the top-level of each (e.g. SELECT
TOP is within the SELECT category).

If you’re on a journey to learn SQL and you’ve been frustrated by the lack of structure or the dull
curriculum, then you may like Dataquest’s interactive SQL skill path. Try it free.

Here are a few SQL courses:

1. Introduction to SQL and Databases

2. Filtering and Sorting Data in SQL
3. Summarizing Data in SQL
4. Combining Tables in SQL
5. SQL Subqueries

List of SQL Commands

SELECT

SELECT is probably the most commonly-used SQL statement. You'll use it pretty much every time you
query data with SQL. It allows you to define what data you want your query to return.

For example, in the code below, we’re selecting a column called name from a table called customers.

SELECT name
FROM customers;

SELECT *

SELECT used with an asterisk (*) will return all of the columns in the table we're querying.

SELECT * FROM customers;

SELECT DISTINCT

SELECT DISTINCT only returns data that is distinct — in other words, if there are duplicate records, it
will return only one copy of each.

The code below would return only rows with a unique name from the customers table.

SELECT DISTINCT name

FROM customers;
SELECT INTO

SELECT INTO copies the specified data from one table into another.

SELECT * INTO customers

FROM customers_backup;

SELECT TOP

SELECT TOP only returns the top x number or percent from a table.

The code below would return the top 50 results from the customers table:

SELECT TOP 50 * FROM customers;

The code below would return the top 50 percent of the customers table:

SELECT TOP 50 PERCENT * FROM customers;

AS

AS renames a column or table with an alias that we can choose. For example, in the code below, we’re
renaming the name column as first_name:

SELECT name AS first_name

FROM customers;

FROM

FROM specifies the table we're pulling our data from:

SELECT name
FROM customers;

WHERE

WHERE filters your query to only return results that match a set condition. We can use this together
with conditional operators like =, >, <, >=, <=, etc.

SELECT name
FROM customers
WHERE name = ‘Bob’;

AND

AND combines two or more conditions in a single query. All of the conditions must be met for the result
to be returned.

SELECT name
FROM customers
WHERE name = ‘Bob’ AND age = 55;
OR

OR combines two or more conditions in a single query. Only one of the conditions must be met for a
result to be returned.

SELECT name
FROM customers
WHERE name = ‘Bob’ OR age = 55;

BETWEEN

BETWEEN filters your query to return only results that fit a specified range.

SELECT name
FROM customers
WHERE age BETWEEN 45 AND 55;

LIKE

LIKE searches for a specified pattern in a column. In the example code below, any row with a name that
included the characters Bob would be returned.

SELECT name
FROM customers
WHERE name LIKE ‘%Bob%’;

Other operators for LIKE:

 %x — will select all values that begin with x

 %x% — will select all values that include x
 x% — will select all values that end with x
 x%y — will select all values that begin with x and end with y
 _x% — will select all values have x as the second character
 x_% — will select all values that begin with x and are at least two characters long. You can add
additional _ characters to extend the length requirement, i.e. x___%

IN

IN allows us to specify multiple values we want to select for when using the WHERE command.

SELECT name
FROM customers
WHERE name IN (‘Bob’, ‘Fred’, ‘Harry’);

IS NULL

IS NULL will return only rows with a NULL value.

SELECT name
FROM customers
WHERE name IS NULL;
IS NOT NULL

IS NOT NULL does the opposite — it will return only rows without a NULL value.

SELECT name
FROM customers
WHERE name IS NOT NULL;

CREATE

CREATE can be used to set up a database, table, index or view.

CREATE DATABASE

CREATE DATABASE creates a new database, assuming the user running the command has the correct
admin rights.

CREATE DATABASE dataquestDB;

CREATE TABLE

CREATE TABLE creates a new table inside a database. The terms int and varchar(255) in this
example specify the datatypes of the columns we're creating.

CREATE TABLE customers (

customer_id int,
name varchar(255),
age int
);

CREATE INDEX

CREATE INDEX generates an index for a table. Indexes are used to retrieve data from a database
faster.

CREATE INDEX idx_name

ON customers (name);

CREATE VIEW

CREATE VIEW creates a virtual table based on the result set of an SQL statement. A view is like a
regular table (and can be queried like one), but it is not saved as a permanent table in the database.

CREATE VIEW [Bob Customers] AS

SELECT name, age
FROM customers
WHERE name = ‘Bob’;

DROP

DROP statements can be used to delete entire databases, tables or indexes.

It goes without saying that the DROP command should only be used where absolutely necessary.
DROP DATABASE

DROP DATABASE deletes the entire database including all of its tables, indexes etc as well as all the
data within it.

Again, this is a command we want to be very, very careful about using!

DROP DATABASE dataquestDB;

DROP TABLE

DROP TABLE deletes a table as well as the data within it.

DROP TABLE customers;

DROP INDEX

DROP INDEX deletes an index within a database.

DROP INDEX idx_name;

UPDATE

The UPDATE statement is used to update data in a table. For example, the code below would update the
age of any customer named Bob in the customers table to 56.

UPDATE customers
SET age = 56
WHERE name = ‘Bob’;

DELETE

DELETE can remove all rows from a table (using ), or can be used as part of a WHERE clause to delete
rows that meet a specific condition.

DELETE FROM customers

WHERE name = ‘Bob’;

ALTER TABLE

ALTER TABLE allows you to add or remove columns from a table. In the code snippets below, we’ll
add and then remove a column for surname. The text varchar(255) specifies the datatype of the
column.

ALTER TABLE customers

ADD surname varchar(255);
ALTER TABLE customers
DROP COLUMN surname;

AGGREGATE FUNCTIONS (COUNT/SUM/AVG/MIN/MAX)

An aggregate function performs a calculation on a set of values and returns a single result.
COUNT

COUNT returns the number of rows that match the specified criteria. In the code below, we’re using *,
so the total row count for customers would be returned.

SELECT COUNT(*)
FROM customers;

SUM

SUM returns the total sum of a numeric column.

SELECT SUM(age)
FROM customers;

AVG

AVG returns the average value of a numeric column.

SELECT AVG(age)
FROM customers;

MIN

MIN returns the minimum value of a numeric column.

SELECT MIN(age)
FROM customers;

MAX

MAX returns the maximum value of a numeric column.

SELECT MAX(age)
FROM customers;

GROUP BY

The GROUP BY statement groups rows with the same values into summary rows. The statement is
often used with aggregate functions. For example, the code below will display the average age for each
name that appears in our customers table.

SELECT name, AVG(age)

FROM customers
GROUP BY name;

HAVING

HAVING performs the same action as the WHERE clause. The difference is that HAVING is used for
aggregate functions, whereas WHERE doesn’t work with them.

The below example would return the number of rows for each name, but only for names with more than
2 records.
SELECT COUNT(customer_id), name
FROM customers
GROUP BY name
HAVING COUNT(customer_id) > 2;

ORDER BY

ORDER BY sets the order of the returned results. The order will be ascending by default.

SELECT name
FROM customers
ORDER BY age;

DESC

DESC will return the results in descending order.

SELECT name
FROM customers
ORDER BY age DESC;

OFFSET

The OFFSET statement works with ORDER BY and specifies the number of rows to skip before
starting to return rows from the query.

SELECT name
FROM customers
ORDER BY age
OFFSET 10 ROWS;

FETCH

FETCH specifies the number of rows to return after the OFFSET clause has been processed. The
OFFSET clause is mandatory, while the FETCH clause is optional.

SELECT name
FROM customers
ORDER BY age
OFFSET 10 ROWS