3. SQL (Structured Query Language) - Part 1

What is SQL ?

SQL (Structured Query Language) is the standard language used to create, read, update, and delete data in a relational database.

Why is SQL important ?

It allows us to communicate with the database and manage data effectively.

Types of Statements (CRUD)

CRUD stands for - Create, Read,Update and Delete.

Let us write the actual code snippets that elucidates the above examples. The reader is strongly advised to try out the examples below. I have created both the Users and Orders table mentioned in chapter 1.

Let us recollect both the CREATE statements used that Let us write the actual code snippets that illustrate the examples above. Readers are encouraged to try out the examples below. I have created both the Users and Orders tables mentioned in Chapter 1.

Let us recall both the CREATE statements used to highlight the CRUD operations discussed above.

CREATE TABLE Users(user_id INT PRIMARY KEY, name VARCHAR (100),
email VARCHAR (80),
password VARCHAR (25)
);

CREATE TABLE Orders(order_id INT PRIMARY KEY, 
user_id INT, 
order_date DATE, 
total_amount DECIMAL(100,2),
FOREIGN KEY (user_id) REFERENCES Users(user_id));

The above two statements when executed will create two empty tables from out fitness domain if they don’t exist. We have highlighted both the primary keys and the foreign key that establishes the relation between the two tables.

INSERT INTO Users (user_id, name, email, password) VALUES
(1, 'Ashwini', 'ashwin@example.com', 'ashwin123'),
(2, 'Zaman', 'zaman@zameen.co.in', 'zami456'),
(3, 'Chandu', 'chandu@fitness.co.za', 'chandu7829'),
(4, 'Damodar', 'damodar@fitlives.com', 'damu321'),
(5, 'Xavier', 'xavier@trainer.com', 'xavier654');

/** The above INSERT statement insert 5 users 

Note: We are inserting passwords in a trivial manner for learning purposes only. Passwords are 
saved with robust encryption techniques.
*/

Executing the above statement inserts 5 rows (records) into the Users table. If you try to execute the above statement it will give an error because the primary key - user_id cannot be repeated ensuring database integrity. The error message looks something like this - SQLITE_CONSTRAINT_PRIMARYKEY: sqlite3 result code 1555: UNIQUE constraint failed: Users.user_id

INSERT INTO Orders (order_id, user_id, order_date, total_amount) VALUES 
(101, 1, '2024-04-01', 199.99), 
(102, 2, '2024-04-02', 49.99), 
(103, 1, '2024-04-05', 129.99), 
(104, 3, '2024-04-10', 79.99), 
(105, 5, '2024-04-12', 249.99);

/** The above INSERT statement inserts 5 Orders. Note
 how the foreign key user_id is between 1 and 5. We used user_id between 1 and 5 in the 
Users table created earlier.

The first ro in the above Orders table will have the order_id 101 that is created by user_id 1
The same user_id has created another order with order_id 103. 
Read this carefully and re-assess.
*/

We have now created two tables with 5 records each. The primary keys and foreign key have neatly ensured the successful relation between Users and Orders entities.

I have used the following online learning playground for SQL. Please register your account and start practising the queries.

After creating both the tables, we use the SELECT queries to see whether we have actually generated the 5 users and Orders properly. I have attached both the screenshots below to showcase the results.

After inserting data, we used the SELECT statement as seen in both the images to check the insertion of data into the tables.

SELECT * —- this means select all the records from the table.

Let us perform the remaining CRUD operations.

We will change the email of the user with user_id = 1

UPDATE Users
SET email = 'ashwini@zankar.com'
WHERE user_id = 1;

I have again highlighted the table of Users with the modification of email for user named “Ashwini”. Please see the snapshot below.

We will now check the DELETE command - the “D” in CRUD operations.

We will delete the User whose user_id is 3.

DELETE FROM Users
WHERE user_id = 3;

The snapshot that depicts this operation is shown below. user_id = 3 stands deleted leaving us with 4 user records!

We have discussed the four CRUD operations.

CRUD = Create + Read + Update + Delete — the four actions we will do most often while handling data from databases.

You might be wondering that I started with database design and diverted my attention to SQL queries. I have done this based on my past experiences. People love to see things in action. When people actually create tables, insert data and then play around with modifications, they gain confidence that we are learning things that are meaningful. This invigorates interest in learning database design.

See you in chapter number 4 with more SQL operations. We will reach the zenith of SQL excitement soon !!