SQL Fundamentals — Service Ops

In today’s fast-paced world of technology and operations, having a solid understanding of SQL is becoming a good to know for operations engineers. SQL (Structured Query Language) is the standard language used to communicate with and manipulate databases, making it a critical skill for anyone involved in managing and maintaining IT infrastructure. In this article, we’ll explore the fundamental concepts of SQL and discuss why ops engineers should prioritize acquiring this knowledge. From data querying to database management, SQL proficiency empowers ops engineers to efficiently handle data-related tasks, troubleshoot issues, and optimize system performance.

Data: Data refers to raw facts, figures, observations, or measurements that are collected, stored, and analyzed for various purposes. It can exist in many forms, including text, numbers, images, audio, video, and more. Data by itself lacks context and meaning; it becomes information only when it is processed and organized to reveal patterns, trends, relationships, or insights.

Database: This is a structured collection of data organized and stored in a computer system. It is designed to efficiently manage and retrieve data according to predefined criteria. Databases are widely used in various applications and industries to store, manipulate, and retrieve data for different purposes.

SQL: SQL, or Structured Query Language, is a powerful and versatile language that forms the foundation of database management and data manipulation. It is the standard language for interacting with relational database management systems (RDBMS) and is widely used in database operations, such as querying, updating, inserting, and deleting data.

SQL Data Type

Some examples of data types in sql:

• Character String (Char, varchar)
• Numeral (Bit, Int, Float)
• Date and Time (Date, Datetime)
• Boolean

SQL DDL (Data Definition Language)

The Data Definition Language is a subset of SQL (Structured Query Language) used to define, modify, and delete database objects such as tables, indexes, views, and constraints. DDL statements are essential for creating and managing the structure of a database.

SQL provides 5 DDL commands:

• Create
• Alter
• Drop
• Truncate
• Rename

CREATE:

To create a simple Database with SQL and add table to the DB:

create table <tablename>
(
column1 datatype,
column2 datatype,
column3 datatyp,
);

Example
create table Student(
StudentId int,
Name varchar(255), //the value 255 specifies the data size
Email varchar(255),
Address varchar(255),
Dept varchar(255));

ALTER:

Used to add, modify and delete columns in a Dataase.

Exampl:
//add a new departnent to the student table:

alter table Student
add Department varchar(255);

Example 2:
//Delete column from student table

alter table Student
drop column Age;

Example 3:
//Do not allow email column to accept NULL value in student table

alter table Student
modify column Email(255) NOT NULL;

To remove / delete and entire table or database:

//delete student table or DB

drop database <database_name>;
drop table Student;

TRUNCATE

To remove old records and add new one, preserve table structure

truncate table <table_name>;

RENAME

rename table <old_tablename> <new_tablename>;

DML (Data Manipulation Language)

DML (Data Manipulation Language) is a subset of SQL (Structured Query Language) used for manipulating data within relational databases. DML is concerned with managing the actual data stored in the database.

There are four DML commands in SQL.

• Select
• Insert
• Update
• Delete

SELECT:

This is used to a set or select data or info in a table. we can retrieve info or view info from a table.

//Retrieve columen name(studentname and studentId) from student table

select column1, column1,
from <table_name>;

Example 1
//Retrieve studnetname & id from table

select studentname, studentId,
from Student;

Example 2
//Retrive all data in table

select *from Student;

INSERT:

To enter a record into a table

insert into <table_name>
values(value1, value2, value3, ..valueN);

Example 1:
//Enter a new student record into Student

insert into Student
values(21, 'Nelson', 0706055);

or
insert into Student
(StudentId, Name, PhoneNo)values(21, 'Nelson', 0706055);

UPDATE:

Update data in columns.

update <table_name>
set column1 = value1, column2 = value2, ...
where condition;

//Replace student with studenId = 3

update Student
set Name = 'Rachael', DeptId = 202,
where studentId= 3;

DELETE:

Remove record from table

delete from <table_name>
where <condition>;

//delete Mark from DB
delete from Student
where studentId = 5;

DCL (Data Control Language)

A component of SQL to control access to DB. DCL is a subset of SQL and it governs access to the database and its objects. DCL is concerned with controlling permissions and security settings. It has two commands:

• Grant
• Revoke

GRANT:

Provides user privileged access to a Database:

grant <priveledge_name>
on <object_name> to {username or public or role_name}
[WITH_GRANT_OPTIONS]

//Example 1
grant create table to Nelson;

//Example 2
//Allow user to view a column in the table
grant select on <studentID> <Nelson> 

REVOKE:

To withdraw access to DB:

revoke <priveledge_name>
on <object_name>
from {user_name or Pulic or role_name}

// Example Revoke 
revoke create table from Nelson

SQL KEYS

SQL keys are essential components of database design that help enforce data integrity and establish relationships between tables. There are several types of keys in SQL:

1. Primary Key (PK): A primary key uniquely identifies each record in a table and ensures that there are no duplicate values. It must be unique and cannot contain NULL values.
2. Foreign Key (FK): A foreign key establishes a link between two tables by referencing the primary key of another table. It ensures referential integrity, meaning that values in the foreign key column must match values in the primary key column of the referenced table.
3. Alternate Key: An alternate key ensures that all values in a column are unique, similar to a primary key. However, unlike a primary key, a table can have multiple alternate keys.
4. Candidate Key: A candidate key is a column or set of columns that can uniquely identify each record in a table. It serves as a potential candidate for the primary key.
5. Composite Key: A composite key consists of multiple columns that, when combined, uniquely identify each record in a table. It is useful when no single column can serve as the primary key.
6. Super Key: A super key is a set of one or more columns that uniquely identify each record in a table. It may contain more columns than necessary to uniquely identify records.

AGGREGATE FUNCTION

Aggregate functions are used to perform calculations on a set of values and return a single result.

Basic SQL provides 5 basic aggregate function:

• avg(): average
• min(): minimum
• max(): Maximum
• sum()
• count()

AVG(), MIN(), MAX()

// Find the average,min,max,no_of_records of mark in student table

select avg(column_name) from table_name

//Example 1
select avg(Marks) from Student 

//Example 2
select min(Marks) from Student 

//Example 3
select max(Marks) from Student 

//Example 4
//Count number of rows
select count(Marks) from Student or
select count(*) from Student

Clauses in SQL

Built in functions for quick examination of data. deal with data easily.

There are four Clauses in SQL:

• Where’
• Group by
• Order by
• Having

Where Clause:

This specifies the condition that has to be obeyed when data is been pulled from the table

Also used to join multiple tables

select <column_name>
from table_name>
where condition;

//retriev details of student with dpt id 102
select *
from Student
where DeptId=102;

//Retrieve data of student with age >=18 and marks<60
select StudentId, Name
from student
where Age=>18 and Marks>60;

Group By

used with select query to arrange identical data in groups.

select <identical column_name>,count(*)
from table_name>
group by <identical column_name>;

//Group by studentId
select studentId,count(*)
from Student
group by studentId;

//Group y Age
select Age,count(Age)
from Student
group by Age;

Order By:

Sort Output in ascending or descending order:

select <column_name>
from <tabke_name>
where condition
order by column_name;

//sort by names in alphaet
select *
from Student
order by Name;

//sort by name in descending order
select *
from Student
order by Name desc;

Having Clause:

Limits query output of group by:

select column_name
from table_name
where condiditon
group by coumn_name
having condition;

//select dept haveing ore than 1 sudent
select deptId,count(*)
from Student
group by DeptId
having count(*)>1;

//Two or more person of equal age
select Age,count(*)
from Student
group by Age
having count(*>1;

SET OPERATIONS IN SQL

A set operator allows the combination of results rom multiple sets into, a single set.

• Union Operator
• Union All Operator
• Intersect Operator
• Mins operator

UNION OPERATOR: Used to combine distinct result from 2 or more select statement, on condition that data types and columns must be same in both tables.

select <colun_name> from table1
union
select <colun_name> from table2

//combine two tales student tables
select * from Student1
union
select * from Student2

UNION ALL

Combines table without removing duplicate rows:

select <colun_name> from table1
union all
select <colun_name> from table2

//combine two tales student tables
select * from Student1
union all
select * from Student2

INTERECT:

Returns common rows from both select statement across 2 tales

select <colun_name> from table1
intersect
select <colun_name> from table2

//combine two tales student tables
select * from Student1
intersect
select * from Student2

MIUS OPERATORS:

Returns record present in the first table but absent in the other

select <colun_name> from table1
minus
select <colun_name> from table2

//combine two tales student tables
select * from Student1
minus
select * from Student2

JOINS IN SQL

These combines rows from two or more table based on a relatable column,

SQL has 7 types of Joins

• Natural Join
• Inner join
• Left join or Left Outer Join
• Right Join or Right Outer Join
• Full join
• Cartesian Join or Cross Join
• Self Join

NATURAL JOIN

Two tables are joined based on all common columns, all attributes will be kept but only one copy on the relatable column will be kept.

select *(for all columns) or <column_Name>
from table1_name natural join table2_name;

//Join student to studedntDpt (look for mathching value)
select *
from Student
natural join StudentDpt;

INNER JOIN

Returns records that have matching values in both tales. the resulting tale contains attribute from both tables.

select table1.column1,table1.column2,table2column1,table2.colun2
from table1
inner join table2
on table1.matching_column=table2.matching_column;

//Example
select *
from Student
inner join StudentDpt
on Student.DptId=StudentDpt.DptId;

LEFTJOIN

Returns all rows from left table even if no matches on right

select table1.column1,table1.column2,table2column1,table2.colun2
from table1
left join table2
on table1.matching_column=table2.matching_column;

//example
student Student.studentId,Student.Name,StudentDpt.DeptId,StudentDpt.studentId
from Student
left join SutdnetDpt
on Student.DptId=StudentDpt.DptId;

RIGT JOIN

Returns all rows on right table even if no matches on the left table

select table1.column1,table1.column2,table2column1,table2.colun2
from table1
rigth join table2
on table1.matching_column=table2.matching_column;

//example
student Student.studentId,Student.Name,StudentDpt.DeptId,StudentDpt.studentId
from Student
right join SutdnetDpt
on Student.DptId=StudentDpt.DptId;

FUL JOIN

Returns rows where there is a only match in one table

select table1.column1,table1.column2,table2column1,table2.colun2
from table1
full join table2
on table1.matching_column=table2.matching_column;

//example
student Student.studentId,Student.Name,StudentDpt.DeptId,StudentDpt.studentId
from Student
full join SutdnetDpt
on Student.DptId=StudentDpt.DptId;

CROSS JOIN

Returns the cartesian products of the set of record from the two or more joined table

select table1.column1,table1.column2,table2column1,table2.colun2
from table1
cross join table2
on Student.DptId=StudentDpt.DptId;

SELF JOIN

Used to multiply a tale with itself

select a.column1,b.column2
from table_name a, table_name b
whee condition;

//Example
select a.StudentID
from Studdent a, Studet b
where a.StudentId=b.SudntId
and a.StudentId<>b.StudentId;

SQL OPERATORS

SQL operators are symbols or keywords used to perform operations on data in SQL queries. They allow you to manipulate and compare data, filter results, and perform calculations. Here are some common SQL operators:

• Arithmetic
• Comparison
• Logical
• Bitwise

ARITHMETIC OPERATORS

//Arithmetic
+
-
*
%
/

//Comparison
=
select * from Students
where Age=20;
>
select * from Students
where Age>17;
>=
select * from Students
where Age>=18;
<=
<> or !=
select * from Students
where Age<>17;
!>
<!

//Bitwise
& AND
select 18 & 16 as result;
| OR
select 18 | 16 as result;
^ Exclusive OR
select 18 ^ 16 as result;

//Logical
All 
And
Any
Between
Exists
In
Like
Not
Is Null
INique

ALL

Compares specific values to all values of another value set

//find student age greater then those with marks above 56
select * from Student
where Age>ALl(SELECT Age from Student where marks>=56);

AND

Allows user to mention multiple conditions and dispaly records if all conditions are true

//Find names of student makrs gretaer than 50 ad age greter than 17
select Name from Student
where Marks>50 AND Age>17;

ANY

Compare specific value to any applicable value

//Find details of student whose Age is greater than age of student with mark abov 56
select * FROM Students
where Age>ANY (SELECT Age from Stdent where Marks>=56);

BETWEEN

Searches for values within a range mentioned, with maximum and minimum values given

//Find names of studnet with age between 16 & 18, 
select * from Student
where Age between 16 AND 18;

EXIST

Search for the presence of a ow in a specified table that meets a certain criteria

//Find detials of student enrolled in more than ne subject
select * from Student
wher StudentId exist(select studentId from Student
where Student.StudentId=Subject.StudentId);

IN

Compare a value to a list of specified literal values

//
select * from Student
where DeptId IN (101, 102);

LIKE

Compares specific value to a similar value using wild card operators

//FInd details of student whose ames starts with A
select * from STudent
 where Name LIKE 'A_%';

NOT

Used with a logical operator to reverse its meaning

//find details of student who is not i Dept 102
select * frostudent
where NOT Dept=102;

ALL

Combines multiple conditions.

//Find details of student with marks greater than 70 or age greater than 70 and not both
select Name from Student
where Marks>70 OR Age>17;

IS NULL

Compares specific value with null value.

//Select names of studet with NULL marks
selct Name from Student
where Marks IS NULL;

UNIQUE OPERATOR

Searches every row of a specified table for unique values

//Find dept ids who have one student
select unique(DeptId)
from Student
where Marks is NOT NULL;

Views in SQL

These are virtual tables based on the result set of a query expression. Imagine you have a table of many columns but you need very few of those columns.

• Used to restrict data access
• Make complex queries easier

CREATE VIEW

create view <view_name> as query_expressin
select column1, column2,..
from table_name
where condition;

//If you want Create view from a single table named student, and you only want 
two column (name & email) where student id is less han 4

//Example 
create view <Student_details> as 
select Name, Email
from Student
where StudentId < 4;

//To see the virtual table created above:
 select * from Student_details;

CREATE VIEW FROM MULTIPLE TABLE

//We want to  create a virtual table that will contain columns from multiple table
//First establish a relationship between the two tables, using a common column

//Example: create a view from tables; Student & Department
create view Student_Dept
select Name, Dept
from Student, Department
where
Student.DeptID=Department.DeptID;

UPDATE VIEW

create or replace view <view_name> as <query_expression>
select column1, column2,...
from table_name
where condition;

//Example
//Add and xtra column to the Student_Dept view
create or replace view Student_Dept
select Name, Dept, Marks
from Student, Department
where Student.DeptID=Department.DeptID

select * from Student_Dept

RENAME VIEW

alter view <view_name>
rename to <new_name>

//Example
alter view Student_Dept
rename to Student_Makrs

DROP VIEW

drop view <view_name>

//Example
drop view Student_Dept

The End!