Connecting you to the next level in life.
CLASS 3
SQL Exercise & Worksheet
www.macappstudiobridge.com
Queries used in the Demo
Table level Queries :
Query 1 - Create a table:
CREATE TABLE `mydatabase`.`usertable` ( `id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL , `username` VARCHAR(100) NOT NULL ,
`password` VARCHAR(100) NOT NULL , `city` VARCHAR(100) NOT NULL , `age`
VARCHAR(100) NOT NULL , `gender` VARCHAR(100) NOT NULL , PRIMARY KEY (`id`))
ENGINE = InnoDB;
Query 2 - Alter a table:
ALTER TABLE `usertable` ADD `age` INT NOT NULL AFTER `city`;
Query 3 - Delete a table:
DROP TABLE usertable
Note : The drop query will delete the table. You need to do the create and alter query again to
proceed further.
CRUD Queries for Records:
Query 1 - Create a new user record :
INSERT INTO `usertable` (`id`, `name`, `username`, `password`, `city`, `age`, `gender`)
VALUES (NULL, 'User 1', 'user1@gmail.com', '123456', 'Chennai', '25', 'Male')
Query 2 - Select a user record with a particular user name and password:
SELECT * FROM `usertable` WHERE `username`='user1@gmail.com' AND
`password`='123456'
Query 3 - Update a user record:
UPDATE `usertable` SET `gender`='Male' WHERE `username`='user2@gmail.com'
Query 4 - Delete a user record:
DELETE FROM `usertable` WHERE `username`='user2@gmail.com'
SQL Exercise
Sl.No Question Answer
1 Enter the query to list the all data in the SELECT * FROM customer
customer details table ? details
_______ * FROM customer details
2 Enter the query to delete the customer details DROP TABLE customer details
table ?
3 Enter the query to Select the records where SELECT * FROM customer
the CustomerID column has the value 12. ? details WHERE
CustomerID=12
4 Enter the query to Insert a new record in INSERT INTO customer details
the customer details table ? (name, email, mobile, city)
VALUES ( 'Bairistow',
_______ customer details (name, email, mobile, 'bairstow@user,com',
city) ______ ( 'Bairistow', 'bairstow@user,com', '9876543219', 'Chennai')
'9876543219', 'Chennai')
5 Enter the query to Set the value of UPDATE customer details SET
the City columns to 'Oslo', but only the ones city='Oslo' WHERE
where the name column has the value name="Bairistow"
"Bairistow". ?
_______ customer details ___ city='Oslo' ____
name="Bairistow"
6 Enter the query to delete all data in the customer DELETE FROM customer
details table ? details
____________ customer details
7 Enter the query to add Address column in ALTER TABLE customer
customer details table ? details ADD Address
VARCHAR(100) NOT NULL
_________ customer details ADD ______ AFTER city
VARCHAR(100) NOT NULL AFTER city
SQL Exercise
Sl.No Question Answer
8 Enter the query to delete 'city' column from ALTER TABLE customer
customer details table ? details DROP COLUMN city
___________ customer details _________ city
9 Enter the query to delete the data where city DELETE FROM customer
column has the value 'chennai' in the customer details WHERE city= 'chennai'
details table ?
____________ customer details _____ city=
'chennai'
To Explore and Study further :
Joins:
1. How to use a JOIN clause in a query to join two or more tables based on a common column in
them.
Reference Material : https://www.w3schools.com/sql/sql_join.asp
Mini-Project Exercises
Tables:
Create one table in the following requirement:
1. Create a table for storing only customer information of a grocery store.
2. Create a table for storing only book information in a library
3. Create a table for storing only food information in a food ordering app
Database:
Create a set of tables for each database in the following requirement:
1. Create a database with a set of tables for online grocery store management
2. Create a database with a set of tables for a food ordering app
3. Create a database with a set of tables for a library management system
To learn more :
SQL Tutorial Links : https://www.w3schools.com/sql/default.asp
SQL Exercise Links : https://www.w3schools.com/sql/exercise.asp?
filename=exercise_select1
WITH FROM
www.macappstudiobridge.com
