A guide to kickstart SQL!!

SQL Venture:

SQL:

          SQL stands for Structured Query Language and its main use is to store, manipulate and retrieve data within relational databases. Just for intuition, here is an instance of a basic relational database.

 Now, no need to worry if you do not understand this diagram. By the end of this article, you will have its complete understanding.

To create a database, there’s a simple query to be run in any of the SQL system. For the sake of this article, we are using MySQL. The query is

                   Create Database ZepAnalytics (DatabaseName)

For using this, we’ll run a query

                    Use ZepAnalytics

SQL Database is a collection of tables, views, stored procedures etc. that store specific set of structured data.

To create a new table

create Table courses(

id int,

course_name varchar(255),

course_instructor varchar(255),

          category varchar(255),

          rating int

)

To view the structure of the table,

                    SELECT * from courses

These are the column names of the table. Now, to insert data in the table,

insert into courses values (1, 'SQL Venture', 'Awon Naqvi', 'Data Science', 5);

insert into courses values (2, 'Regression vs Classification', 'Adam', 'Artificial Intelligence', 4);

insert into courses values (3, 'CNN', 'Muhammad', 'Deep Learning', 5);

insert into courses values (4, 'Power BI', 'Ali', 'Data Visualisation', 4.5);

insert into courses values (5, 'MongoDB', 'Hassan', 'Data Science', 4)

Here, we have used insert function to add new values to the table. To check the structure of the table, again,

                   SELECT * from courses

Moving on, we’re going to create a basic relational database with the help of the ER-diagram given in this example. We are creating a schema first. An instance of SQL Server can contain one or many databases. Inside a database, there are one or numerous object ownership groups called schemas. Inside each schema, there are database objects such as tables, views, and stored procedures.

In relational databases, there’s a concept of primary and foreign keys. A primary key allows each record in a table to be uniquely recognized. There can merely be one primary key per table. A foreign key can be applied to one column or numerous and is utilized to interlink 2 tables together in a relational database. The tiny PK and FK in the tables refer to Primary Key and Foreign Key respectively.

We’re going to create two tables under one schema and interlinking them with foreign key.

                   Create schema Production

Firstly, we are going to create the table which is independent of the other i.e Production.Category, where Production refers to the schema and Category refers to the table’s name.

create table Production.Category

(

CategoryName varchar(50) not null,

          CategoryId int not null,

          constraint Pk_Category_id primary key(CategoryId)

)

Now, creating the Product Table:

create table Production.Product

(

ProductId int not null,

ProductName varchar(50),

          CategoryId int not null,

          primary key (ProductId),

          foreign key (CategoryId) references Category(CategoryId)

)

Here, int and varchar refer to integer and variable character of the maximum length respectively. And there’s a reference of the parent table and its inherited candidate key’s column name.

Order By:

select * from courses

order by rating

Here, as we can see, the rows have been ordered on the basis of rating.

Select Distinct:

SELECT DISTINCT category FROM courses

Here, only distinct categories are shown excluding any duplicate value.

WILDCARDS:

%

select * from courses

where course_instructor like'%ad'

Here, it shows the course instructor’s name that ends with ‘ad’ and has something before it.

select * from courses

where course_instructor like'ad%'

Here it shows the name of the course instructor that starts with ‘ad’ and has something after it

select * from courses

where course_instructor like'%am%'

Here it shows the name that has ‘am’ anywhere in it.

( _ ):

The underscore sign equates any single character. In the example below, the course name is given with 4 underscores

select * from courses

where course_name like'____Venture'

View:

A view is essentially a SQL results set that gets stored in the database under a label, so you can return to it later, without having to re-run the query.

CREATE VIEW Zep AS

SELECT * FROM courses

WHERE rating = 5

Then afterwards, if you need to access the stored view, you’ll do so like this:

SELECT * FROM Zep

REPLACING/UPDATING VIEW:

With create or replace view command, we can update the view’s result set.

CREATE OR REPLACE VIEW Zep AS

SELECT * FROM courses

WHERE rating = 5 AND category LIKE 'Data%'

To view the result

SELECT * FROM Zep

CONCLUSION:

SQL is a language of great use and is in use of every data-driven company, as it allows us to create more complex, functional websites and systems.