untung99.homes: The Ultimate Guide on SQL Basics

Basic Queries in SQL

In this section, we will run some exciting commands to start with on the MySQL Workbench. 

It lists the databases present on the MySQL Workbench server host. 

Syntax:

show databases;

The following SQL query will show all the databases present on MySQL Workbench server host.

Show databases;

The USE Statement selects a database and performs SQL operations on that database.The database remains default until the end of session or execution of another USE statement with some other database.

Syntax:

USE database_name;

The following SQL query will let you perform operations on the selected mysql database.

Use mysql;

The CREATE TABLE statement is used in SQL to create a new table.

Syntax:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    column3 datatype,

   ….

);

The following SQL query will create a customer table with attributes: customer id, customer name, age, gender, date of purchase, address, item, and price.

create table customers (Cust_Id int primary key, Cust_Name varchar(25),

Age int, Gender char(1), Dop date, Address varchar(20), Item varchar(15), Price float);

The INSERT INTO statement is basically used to add new data in a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, …)

VALUES (value1, value2, value3, …);

The following SQL query will insert data into the records in the customers table.

insert into customers values

(101, “Joseph”, 22, “M”, “2016-11-23”, “Pune”, “Vegetable”, 80),

(102, “Nilesh”, 21, “M”, “2016-11-23”, “New York”, “softdrink”, 800),

(103, “Vipul”, 33, “M”, “2016-11-23”, “Miami”, “candies”, 620),

(104, “Anubhav”, 61, “M”, “2016-11-23”, “Pune”, “fruits”, 250),

(105, “Utkarsh”, 45, “M”, “2016-11-23”, “New York”, “snacks”, 380),

(106, “Ishan”, 44, “M”, “2016-11-23”, “Miami”, “potato”, 60),

(107, “Ankit”, 52, “M”, “2016-11-23”, “Chicago”, “ice cream”, 220),

(108, “Akshay”, 36, “M”, “2016-11-23”, “Pune”, “coffee”, 80),

(109, “Akash”, 28, “M”, “2016-11-23”, “New York”, “Vegetable”, 150),

(110, “Tanishq”, 19, “M”, “2016-11-23”, “Miami”, “noodles”, 400),

(111, “Raghav”, 24, “M”, “2016-11-23”, “Detroit”, “cups”, 3400),

(112, “Anand”, 54, “M”, “2016-11-23”, “Pune”, “watermelon”, 680),

(113, “Amrit”, 43, “M”, “2016-11-23”, “Miami”, “facewash”, 340),

(114, “Andrew”, 18, “M”, “2016-11-23”, “Boston”, “shampoo”, 950),

(115, “Ricky”, 26, “M”, “2016-11-23”, “Detroit”, “vanilla”, 290),

(116, “Cummins”, 30, “M”, “2016-11-23”, “Chicago”, “mango”, 640),

(117, “Rabada”, 40, “M”, “2016-11-23”, “Boston”, “banana”, 80),

(118, “Johnson”, 55, “M”, “2016-11-23”, “New York”, “batteries”, 420),

(119, “Adam”, 45, “M”, “2016-11-23”, “Boston”, “router”, 160),

(120, “Illeana”, 25, “F”, “2016-11-23”, “Chicago”, “brinjal”, 550);

The most commonly used SQL command is the SELECT statement. SQL SELECT statement is used to select rows and columns from a table in the database.

Syntax:

SELECT column1, column2, …

FROM table_name;

The following SQL query will return all the records of the customers table.

Select * from customers;

The following SQL query will return the columns customer id and customer name of all the records from the customers table.

select Cust_id,Cust_Name from customers;

The DROP Statement is basically used to delete a particular table. Dropping of the table will drop all the objects (tables, views, procedures, etc.) inside it. The user should have admin privileges for deleting a table.

Syntax:

DROP TABLE table_name;

The following SQL query will drop or delete the customers table from the database.

Drop table customers;

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

Syntax:

TRUNCATE TABLE Categories;

The following SQL query will delete all the records from the customers table.

truncate table customers;

ALTER TABLE command is used to update the structure of a table by updating the definition of its columns. The ALTER command can perform the following functions:

1) Add, drop, modify table columns

2) Add and drop constraints

3) Enable and Disable constraints

Syntax:

ALTER TABLE table_name ADD column_name datatype;

The following SQL query will return the customers table without an age column.

alter table customers drop column age;

A DELETE statement is used to delete fields from a table.

Syntax:

DELETE FROM table_name WHERE condition;

The following SQL query will delete the record with customer id = 102.

delete from customers where Cust_Id=102;

The UPDATE statement is basically used to update the existing records in a table.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2, …

WHERE condition;

The following SQL query will update the customer name to James and city name as Boston in the record with customer id = 101.

update customers set Cust_Name = “James”, Address= “Boston” where Cust_Id = 101;

The SELECT DISTINCT statement in SQL is used to return only unique values from a column.

Syntax:

SELECT DISTINCT column1, column2, …

FROM table_name;

The following SQL query will return the unique cities where customers reside.

select distinct address from customers;

The MIN() function returns the smallest value of the selected numeric column in a table.

Syntax:

SELECT MIN(column_name)

FROM table_name

WHERE condition;

The following SQL query will return the minimum price spent by the customer.

select MIN(Price) AS LessPrice from customers;

The MAX() function returns the largest value of the selected numeric column.

Syntax:

SELECT MAX(column_name)

FROM table_name

WHERE condition;

The following SQL query will return the maximum price spent by the customer.

select MAX(Price) AS MaxPrice from customers;

The SUM() function gives the total sum of a numeric column. 

Syntax:

SELECT SUM(column_name)

FROM table_name

WHERE condition;

The following SQL query will return the sum of the price spent by the customers.

select SUM(Price) from customers;

The AVG() function gives the average value of a numeric column. 

Syntax:

SELECT AVG(column_name)

FROM table_name

WHERE condition;

The following SQL query will return the average price spent by the customers.

select AVG(Price) from customers;

The GROUP BY clause is used to return data that is grouped according to one or more columns.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s);

The below SQL statement counts the number of customers from each city.

select count(Cust_Id), Address from customers group by Address;

The HAVING clause is mostly used with aggregate functions. Having clause filters the records from groups based on specified conditions.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

HAVING condition

ORDER BY column_name(s);

The following SQL query will return the cities where there are more than three customers.

select count(Cust_Id), Address from customers group by Address having count(Cust_Id)>3;

The ORDER BY clause is used within a SELECT statement to extract output either in ascending or descending order of columns.

Syntax:

SELECT column-list

FROM table_name [WHERE condition]

[ORDER BY column1 [, column2, .. columnN] [DESC]];

The following SQL query will return the cities in an alphabetical order.

select * from customers order by address;