Basic SQL in One Blog

Introduction:

Hello, everyone! I must admit, I'm not much of a writer, but I love taking up challenges. This blog is primarily intended to develop my skills and keep learning. So, let's embark on this journey together as I help you learn SQL – the structured query language, a crucial skill for various job roles. Get ready for an exciting SQL ride!

What is SQL and RDBMS?

SQL, or structured query language, is the core language used for relational databases. It enables users to access and manage data efficiently. On the other hand, RDBMS (Relational Database Management System) is an application that allows users to create, delete, and interact with a collection of data in a structured way.

SQL Commands:

1. DQL (Data Query Language): The principal DQL command is SELECT, used to retrieve data from the database.

2. DDL (Data Definition Language): DDL commands enable users to perform various operations on the database, such as creating, altering, and dropping tables.

3. DML (Data Manipulation Language): DML allows you to access and manipulate data. Common DML commands include insert, update, delete, call, and lock.

4. DCL (Data Control Language): DCL grants users the ability to control access to the database by using commands like grant and revoke.

5. TCL (Transaction Control Language): TCL is used to control the execution of transactions. Commands like commit and rollback ensure data consistency.

Takeaway:

In the upcoming sessions, we will delve deeper into SQL and PostgreSQL. Stay tuned to learn more about executing queries and various other SQL concepts.

Let's get hands-on! To create a table, use the syntax: "CREATE TABLE table_name (column_name data_type)." For example:

Here, I am creating a table called "user1":

Drop the table if it exists

DROP TABLE IF EXISTS user1;

Create the user1 table with the specified columns

CREATE TABLE user1 (

id INT PRIMARY KEY,

firstname VARCHAR(20),

lastname VARCHAR(20),

Generation VARCHAR,

Date_of_birth DATE,

Age INT,

gender VARCHAR(10)

);

Next, you may wonder about data types. Data types define the kind of values you can store in a column. Here are some main data types:

- Integer: Stores whole numbers

- Varchar: Stores text of varying length

- Float: Stores floating-point numbers

- Decimal: Stores fixed-point numbers

- Time: Stores time values

- Datetime: Stores date and time values

- Date: Stores date values

One important topic to cover is constraints. A primary key should always be unique, and we have another constraint called "not null."

After creating the table, we need to insert values into it using the DML (Data Manipulation Language) command "INSERT". For example:

INSERT INTO user1 VALUES

('1', 'mahadeera', 'raja', '1st', '06-17-1964', '59', 'male'),

('2', 'Rani', 'rama', '1st', '03-18-1968', '55', 'Female'),

('3', 'Venkat', 'Raja', '2nd', '10-13-1990', '33', 'male'),

('4', 'malar', 'raja', '2nd', '03-02-1995', '28', 'female');

Additionally, let's insert a few more values into the "user1" table:

`

INSERT INTO user1 VALUES

('5', 'raja', 'rama', '2nd', '06-26-1988', '35', 'male');

To make changes to the table schema, we use the DDL (Data Definition Language) command "ALTER". For instance, let's add a new column "city" to the "user1" table:

ALTER TABLE user1

ADD city VARCHAR;

To rename the "city" column to "country":

ALTER TABLE user1

RENAME COLUMN city TO country;

We can also perform other operations like modify and drop using the "ALTER" command. For the "country" column, we will add values:

UPDATE user1

SET country = 'ind'

WHERE id IN ('2', '3', '4');

Now, let's create another table named "expand" and add a foreign key "user1_id":

CREATE TABLE expand (

id INT PRIMARY KEY,

firstname VARCHAR(20) NOT NULL,

lastname VARCHAR(20),

Generation VARCHAR,

Date_of_birth DATE,

Age INT,

gender VARCHAR(10),

country VARCHAR(10)

);

ALTER TABLE expand

ADD user1_id INT;

Finally, let's insert values into the "expand" table:

INSERT INTO expand VALUES

('1', 'raja', 'rama', '2nd', '06-26-1988', '35', 'male', 'ind', '5'),

('2', 'moon', 'raja', '3rd', '06-19-2019', '4', 'female', 'finland', 'null'),

('3', 'sun', 'raja', '3rd', '10-29-2021', '2', 'female', 'finland', 'null');

The next most interesting topic we're going to see is what joins are, why they are important, and how they work. As I mentioned, we use SQL in RDBMS, and there are two types of databases:

RDBMS and non-relational databases.

In RDBMS, we use MySQL, Postgres, or Oracle, while non-relational databases include MongoDB. Both types of databases store, retrieve, analyze, and manage data, but there are differences. RDBMS allows for relational connections between tables by utilizing primary key and foreign key concepts. ACID is performed to ensure data integrity, where A stands for atomicity (a set of operations either happen all at once or not at all), C stands for consistency (the database must be consistent before and after transactions), I stand for isolation (multiple transactions occur independently without any interference), and D stands for durability (the changes of successful transactions persist even in the case of system failures). On the other hand, non-RDBMS stores data in a tabular form within one single table, and primary keys allow access to the data. It is faster, but security may not be as robust.

Now, let's focus on joins, which allow us to combine data from two or more tables based on related columns. There are four types of joins in PostgreSQL:

1. INNER JOIN: This brings matching records from both tables. Example:

SELECT * FROM user1 u

INNER JOIN expand e ON u.id = e.user1_id;

2. LEFT JOIN: This brings all data from the left table and matching records from the right table.

SELECT * FROM user1 u

LEFT JOIN expand e ON u.id = e.user1_id;

3. RIGHT JOIN: This brings all data from the right table and matching records from the left table.

SELECT * FROM user1 u

RIGHT JOIN expand e ON u.id = e.user1_id;

4. FULL JOIN: This brings all data from both tables.

`

SELECT * FROM user1 u

FULL JOIN expand e ON u.id = e.user1_id;

All the way towards finishing the blog, let's discuss the operations we can perform with the SELECT query: SELECT, FROM, WHERE, ORDER BY, GROUP BY, and HAVING. Additionally, let's explore the set operators: UNION, UNION ALL, EXCEPT, and INTERSECT.

1. SELECT: It is used to retrieve data from a table and specify the columns you want to display in the result.

2. FROM: It indicates the table from which the data should be retrieved.

3. WHERE: It is used to filter the data based on specific conditions.

4. ORDER BY: It is used to sort the result in ascending or descending order based on a column.

5. GROUP BY: It groups the rows based on a column to perform aggregate functions.

6. HAVING: It filters the grouped results based on specified conditions.

SELECT COUNT(lastname)

FROM user1

GROUP BY gender

HAVING COUNT(lastname) >= 3;

Finally, let's explore the set operators, including UNION, UNION ALL, EXCEPT, and INTERSECT, which allow us to combine the results of two or more SELECT queries.

The data types in the columns must also be comparable.

In each SELECT statement, the columns must be in the same order.

Next, let's explore the set operators:

1. UNION: It combines the results of two SELECT queries and removes duplicate rows from the result set.

2. UNION ALL: It combines the results of two SELECT queries and retains all rows, including duplicates.

3. EXCEPT: It includes values from the first query but removes any matching values found in the second query.

4. INTERSECT: It includes only the matching values from both tables in the result.

Example:

SELECT firstname, lastname, gender FROM user1

UNION

SELECT firstname, lastname, gender FROM expand;

SELECT firstname, lastname, gender FROM user1

UNION ALL

SELECT firstname, lastname, gender FROM expand;

SELECT firstname, lastname, gender FROM user1

EXCEPT

SELECT firstname, lastname, gender FROM expand;

SELECT firstname, lastname, gender FROM expand

EXCEPT

SELECT firstname, lastname, gender FROM user1;

SELECT firstname, lastname, gender FROM user1

INTERSECT

SELECT firstname, lastname, gender FROM expand;

Conclusion:

In this blog, we learned the basics of SQL, including creating tables, data types, constraints, and manipulating data with DML commands. We also delved into the importance of RDBMS and non-relational databases, their differences, and how ACID properties ensure data integrity. Joins played a vital role in combining data from multiple tables, enhancing our ability to analyze data effectively. Lastly, we explored various operations that can be performed using SELECT queries, and how set operators allow us to perform set operations on query results. SQL is a powerful tool in data management and analysis, and with continuous learning, we can master this valuable skill and excel in various job roles.