DBMS For Application Development: SQL (Structured Query Language)

Photo by Chris Liverani on Unsplash

SQL is a programming language used to store, retrieve and manipulate data in a database.

SQL is also used to define the schema of a database. When defining the schema for a table you can specify:

• Data type of each attribute in the table
• The constraints on the attribute
• Name of each table column

The basic data types of SQL are:

• char(short for character) - a fixed length string. The length is specified by the user.
• varchar(short for character varying) - is a variable length with a user defined max length
• int - an integer
• smallint - a smaller integer
• numeric - a fixed point number with a user defined precision number.
• real, double precision - floating-point and double-precision floating point numbers
• float - a floating point number with a user defined precision

int, smallest, real and double precision data type are machine dependent. The max length and precision of these data type are defined by the computer architecture on which the databases is stored.

To create the schema for a table in SQL we use the create table command.
Let’s define the the schema for a few tables in our photo sharing application.

CREATE TABLE user(
    user_id INTEGER NOT NULL AUTO_INCREMENT,
    username VARCHAR(30),
    password VARCHAR(30),
    email_add VARCHAR(32),
    fullname VARCHAR(30),
    fb_id INTEGER,
    twitter_id INTEGER,
    profile_pic_url VARCHAR(30),
    privacy_level BOOLEAN,
    tag_option BOOLEAN,
    primary key (user_id, username, email_add),
);

CREATE TABLE followers(
    uid INTEGER
    follower_id INTEGER
    timestamp DATE
    rejectdenyoption INTEGER,
    foreign key (uid) references user
);

CREATE TABLE following(
    uid INTEGER
    follower_id INTEGER
    timestamp DATE
    rejectdenyoption INTEGER,
    foreign key (uid) references user
);

To insert data into our newly created tables we use:

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

We can omit the column name:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

But we must put the value in the order as defined in the table schema.

Let's add a few records to our user table.

INSERT INTO table_name (username, password, email_add, fullname, fb_id, twitter_id, profile_pic_url, privacy_level, tag_option)
VALUES (Jame_Bond_007, fakepassword, '[e-mail]' , 'Jame Bond' , 1847123, 1847123, 'https://cdn.photoshare.com', true, false);

INSERT INTO table_name (username, password, email_add, fullname, fb_id, twitter_id, profile_pic_url, privacy_level, tag_option)
VALUES (LilLeafHurricane, fakepassword, '[e-mail]' , 'Rock Lee' , 3547126, 3547126, 'https://cdn.photoshare.com', true, false;

Now we have data in our table. We need to retrieve that data to show it in our application. A query is a request to get data from our database. An SQL query consists of three parts, select, from, and where. The from clause tells us which tables we are querying. The select clause tell us which columns we want to output from our query results. The where clause is used to set condition on our query output.

Let’s do a few query on our photo sharing application database.

-- Select all user with private account
select username, email_add, profile_pic_url from user where privacy_level = true;

-- Get a specific user data
select * from user where username = 'username';

-- Get all user photo [Return only photo_id],user id is random
select photo_id from photo where uid = 98494

-- Get a specific user follower [Return follower id]
select follower_id from followers where uid = 64736 and follower_id = 12342

-- Get a specific user follower [Return follower id]
select follower_id from followers where uid = 64736 and follower_id = 12342

SQL provides aggregation functions, aggregation functions takes a collection as input and return a single value.

• Average: avg
• Minimum: min
• Maximum: max
• Total: sum
• Count: count

Of all the aggregation function sum and avg can only have numbers as input.

Here are a few examples using aggregation functions.

-- Count all likes on a specific photo
select sum (photo_id) as likes from likes where photo_id= 090982;

-- Count all user followers
select sum (uid) as likes from followers where uid = 090982;

-- Count all people who user is following
select sum (uid) as likes from following where uid = 090982;

There’s a lot of use case for the other aggregation function but to keep the examples simple I use sum since it’s use case is very simple in this application.

SQL provides the set operations intersect, union and except that allows you to perform set operations on two tables. The union operations combines the result of two queries into a single table. Any duplicates are eliminated. The intersect operation takes the the results of two queries and return only the records that are in both query result. The except operation takes the unique rows of a query results and returns the rows that do not appear in the second result set, except does not remove duplicates.

Here is an example of a set operation using intercept to get all the liked photos two users share in common:

-- Return the photo_id of all the images two user both like
SELECT photo_id
FROM   likes
WHERE  likerUID = 12345;
INTERSECT
SELECT photo_id
FROM   likes
WHERE  likerUID = 67890;

SQL is a programming language used to interact with data in a database. SQL have special functions called aggregate functions that you can use to apply to a query results to returns a single output. You can perform set operations on the result of multiple queries to be combined into a single result.

Useful Resources:

• SQL - Full course for beginners