IT Beginner Series: SQL Exercises
More in the IT Beginner Series
Introduction
SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system. It is particularly useful in handling structured data, i.e.Wikipedia
In short, it’s the language we use to create a database, put data into it, read that data and so on.
Below you can find some exercises which should take you through all the basic SQL commands which you can use to get familiarized with the syntax and concepts.
You can use W3Schools SQL Editor to play around with SQL commands directly in your browser: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
Anatomy of a database table
Syntax
Database entities
- Table: collection of rows
- Row (or record): actual data
- Field (or cell): data member
- Column: name of the fields
Keywords for manipulating data:
- SELECT — query/select data in one or more tables
- INSERT — add values to a table
- UPDATE — edit/update values in a table
- DELETE — remove data from a table
Keywords for manipulating tables:
- CREATE — create a new table
- ALTER — edit/update the structure of a table(add/remove columns, change column datatype)
- DROP — remove a table
Exercises with solution
#1 Create a table called ShopProducts with the following columns: ProductID, ProductName, Price, DateAdded
--create table
CREATE TABLE ShopProducts(
ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
ProductName varchar(50),
Price decimal,
DateAdded timestamp DEFAULT CURRENT_TIMESTAMP)#2 Insert a couple of values into the ShopProducts table
--insert values into a table
INSERT INTO ShopProducts(ProductName,Price)
VALUES
('Iphone 13 Pro Max', 1800),
('S22 Ultra', 1500),
('3310', 9000),
('Xperia X', 500)#3 Select all columns from the Products table
--select values from all(*) columns of a table
SELECT * FROM ShopProducts#4 Select only certain columns
--select values from only certain columns
SELECT ProductName, Price FROM ShopProducts#5 Select ProductName and Price for products with the price lower than 10
--select values that based on a condition
SELECT ProductName, PRICE FROM ShopProducts WHERE PRICE < 1600#6 Select ProductName and Price for products with the Price between 1000 and 2000
--select values that based on multiple conditions
SELECT ProductName, Price FROM ShopProducts WHERE Price > 1000 AND Price < 2000#7 Calculate the sum of all prices
--select the sum of all values in a column
SELECT SUM(Price) FROM ShopProducts#8 Select the number of rows in the table
--select the number of rows in a table
SELECT COUNT(*) FROM ShopProducts#9 Select the maximum value of a column
--select the maximum values of a column
SELECT ProductName, MAX(Price) FROM ShopProducts#10 Order data by Price(run the queries one by one)
--order data ascending
SELECT * FROM ShopProducts ORDER BY Price ASC--order data descending
SELECT * FROM ShopProducts ORDER BY Price DESC
#11 Update the price by 20% for product with ID 1
--update product price
UPDATE ShopProducts SET Price = 1.2 * Price WHERE ProductID=1# 12 Delete the product with product name “Nokia 3310”
--delete product
DELETE FROM ShopProducts where ProductName = 'Xperia X'#13 Add a new column named BrandID to the ShopProducts table
--modify table structure
ALTER TABLE ShopProducts
ADD BrandID integer#14 Create a new Brands table
CREATE TABLE Brands
(BrandID INTEGER PRIMARY KEY AUTOINCREMENT,
BrandName varchar(50),
BrandCountry varchar(50))#15 Add some brand details in the newly created Brands table
INSERT INTO Brands (BrandName, BrandCountry)
VALUES ('Samsung', 'Korea'),
('Apple', 'US'),
('Nokia', 'Finland')#16 Update the ShopProducts BrandID column with the correct BrandID from the Brands table(run commands one by one)
UPDATE ShopProducts SET BrandID = 1 WHERE ProductName = 'Samsung S22 Ultra'UPDATE ShopProducts SET BrandID = 2 WHERE ProductName = 'Iphone 13 Pro Max'
#17 Show data from both ShopProducts and Brands tables by joining the tables
--join data from two tablesSELECT ProductName, BrandName, Price
FROM ShopProducts
INNER JOIN Brands ON ShopProducts.BrandID = Brands.BrandID
Good luck,
Andrei
