Introduction to database

23 January 2024 13:20

A very straightforward description of a database is that it is a form of electronic storage in which data is held.

Data :
What is data? In basic terms, data is facts and figures about anything. For example, if data were collected on a person, then that data might
include their name, age, email, and date of birth. Or the data could be facts and figures related to an online purchase. This could be the
order number, description, order quantity, and date, and even the customer's email. Data is crucial for individuals as well as organizations.

DataBases :
Where's all this data stored? In our digital world, data is no longer stored in manual files. Instead, developers use something called
databases. A database is a form of electronic storage in which data is organized systematically. It stores and manipulates data
electronically to make it more manageable, efficient, and secure.

Graph databases store data in the form of nodes. In this

case, entities like customers, orders, and products are
An object-oriented database is where data is stored in the form of represented as nodes. The relationships between them Finally, there's document databases where data is stored as JSON or
objects instead of tables or relations. An example of this kind are represented as edges JavaScript Object Notation objects. The data is organized into
of database could be an online bookstore. The store's database collections like tables. Within each collection a documents written in
could render authors, customers, books, and publishers as JSON that record data. In this example, customer documents are
classes like sets or categories. The objects or instances of these held in a customer collection while ordering product documents are
classes would then hold the actual data. stored in the ordering product collections.

DataBases_SQL Page 1
 Data stored in a database cannot exist in isolation. It must have a relationship with other data so that it can be processed into meaningful information

Can a primary key be used to identify a table field that contains unique A foreign key is a field in one table that connects to the primary key field in the original
values? -- YES table, which in this case is the customer table. The customer ID is the primary key of the
Even if two customers share the same name, they'll still have separate customer table, but it becomes a foreign key in the order table. This way, the
customer IDs. This means that your database can determine which relationship is established and the data in these two tables are related.
customer is the required one.

"a picture is worth a thousand words".

It’s important to organize data, process it, and present it efficiently to make it more useful and meaningful to people. The way data is related and presented enables people to
form a better understanding of existing data.

DataBases_SQL Page 2
 No sequel databases are used by social media platforms, the
Internet of Things,
data types of no sequel databases include document artificial intelligence and other applications that generate massive
databases. amounts
Key value databases and graph databases. of unstructured data types of no sequel databases include
document databases.

Where does this kind of complex data come from?

Social media platforms, Online shopping sites and other services generate massive amounts
of data every second of the day as they capture the actions of billions of users around the
world and with the internet of things or IOT more and more devices are connected to the
Internet, generating even more and more data. This is how complex data or big data is
created.
All this data is highly unstructured or semi structured.

DataBases_SQL Page 3
DataBases_SQL Page 4
SQL
24 January 2024 19:26

As a data engineer, you can interact with databases Some of the operations you could carry it in the data might require you to
using Structured Query Language or as it's more commonly create, read, update, and delete data. These operations are also known as
known, SQL, CRUD operations

As a web developer, you'll execute all SQL instructions on a database using a DBMS. The DBMS
takes responsibility for transforming SQL instructions into a form that's understood by the
underlying database.

DDL as the name says helps you define data in your database. But what does it mean to define data? Before you can store data in Data manipulation language or DML commands are used to man
the database. You need to create the database and related objects like tables in which your data will be stored. For this, the DDL part updating or deleting data. Most crude operations fall under DML.
of SQL has a command named create. Then you might need to modify already created database objects. command. This command lets you specify the fields to add data
For example, need to edit data that's already inserted into a table, just deploy t
you might need to modify the structure of a table by adding a new column. You can perform this task with the DDL alter be removed by using the delete command. So far, you've learne
command. You can remove an object like a table from a database using the DDL drop command. within them.

The SQL DML commands provide the ability to q

INSERT Command : To add records of d

DataBases_SQL Page 5
 nipulate data in the database, like inserting
. To add data to a table, you can use the insert So how do you read or retrieve that data?
too along with the values to be inserted. If you To read data stored in a database, you can use data query language. finally, you can also use DCL or data control language to contr
the update command. And you can specify data to DQL defines a select command to be able to retrieve data. Select lets you retrieve data from one or For example, using DCL commands,
ed how to add database objects and manage data multiple tables letting you specify the data fields that you want based on preferred filter criteria. you control access to data stored in the database. Grant and re
give users access privileges to data,
and to revert access privileges already given to users

query, delete and update data in the database. Use the following commands in this category.

You need to populate a table in your database. Which of the following SQL subsets should you use to The SQL DQL commands provide the ability to query and retrieve data from the database. Use the
add the data? following command in this category.

Data Definition Language

Data Query Language

Data Manipulation Language

https://www.coursera.org/learn/intro-to-databases-back-end-
development/supplement/ecDvl/additional-resources

DELETE Command: To delete data from a table in the database.

UPDATE Command : To modify or update data contained within a table in the database.
data into an existing table.

DataBases_SQL Page 6
trol access to the database.

evoke DDL commands are used to

DataBases_SQL Page 7
 SELECT Command: To retri

In aggregation Group BY is Imp

DataBases_SQL Page 8
ieve data from tables in the database.

DataBases_SQL Page 9
DataBases_SQL Page 10
 As you now know every column has a data type.
The data type of a column defines what type of value a column can hold like
integer character, date and time and so on.
It's up to the developer to decide the data type for each column.

From <https://www.coursera.org/learn/intro-to-databases-back-end-
development/lecture/7VpMb/what-are-tables-in-databases>

Generally all database systems support string data types for storing
characters
and strings of characters, numeric data types to store exact or whole
numbers and
approximate numbers, date and time data types to store information on
date and
time and binary data types to store images, files and other information.

From <https://www.coursera.org/learn/intro -to-databases-back-end-

development/lecture/7VpMb/what -are-tables-in-databases>

DataBases_SQL Page 11
DataBases_SQL Page 12
DataBases_SQL Page 13
 In relational database terminology a table is also known as a relation. A table row or a record is also known as a tuple. For
example, the student relation above has six tuples.

From <https://www.coursera.org/learn/intro -to-databases-back-end-development/supplement/UjZNO/tables -overview>

Schema simply means the structure. The structure includes:

• the name of the table orFrom
relation,
<https://www.coursera.org/learn/intro-to-databases-back-end-development/supplement/UjZNO/tables-overview>

• its attributes,
• their names
• and data type.

The foreign key is an attribute on the table that references a unique key
in
another table.
Typically a foreign key reference is the primary key of another table.

From <https://www.coursera.org/learn/intro-to-databases-back-end-
development/lecture/pFwgX/types-of-keys-in-a-database-table>

DataBases_SQL Page 14
DataBases_SQL Page 15
DataBases_SQL Page 16
 However multi value attributes sh
relational database design.

DataBases_SQL Page 17
ould be avoided in

DataBases_SQL Page 18
DataBases_SQL Page 19
SQL add on
01 February 2024 01:45

DataBases_SQL Page 20
DataBases_SQL Page 21
 you could use an INSERT INTO select statement to query data in column C in the source
table and place the results of that query in column B of the target table.

DataBases_SQL Page 22
DataBases_SQL Page 23
Database Schema
06 June 2024 20:03

DataBases_SQL Page 24
DataBases_SQL Page 25
DataBases_SQL Page 26
Question Solving
14 February 2024 22:18

DataBases_SQL Page 27
DataBases_SQL Page 28
Window Function and
CTE in SQL
19 September 2024 23:00

https://www.geeksforgeeks.org/cte-in-sql/ --- CTE in SQL

https://www.geeksforgeeks.org/window-functions-in-sql/ ---- Window function

DataBases_SQL Page 29
CTE

DataBases_SQL Page 30
SQL 50 -------- START-----------
26 June 2024 10:01

DataBases_SQL Page 31
1661. Average Time of Process per Machine
01 July 2024 16:37

SELECT *
FROM Activity a0 JOIN Activity a3 ON
a0.machine_id = a3.machine_id AND a0.process_id = a3.process_id;

Activity table: Activity table:

+------------+------------+---------------+-----------+ +------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp | | machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+ +------------+------------+---------------+-----------+
|0 |0 | start | 0.712 | |0 |0 | start | 0.712 |
|0 |0 | end | 1.520 | |0 |0 | end | 1.520 |
|0 |1 | start | 3.140 | |0 |1 | start | 3.140 |
|0 |1 | end | 4.120 | |0 |1 | end | 4.120 |
|1 |0 | start | 0.550 | |1 |0 | start | 0.550 |
|1 |0 | end | 1.550 | |1 |0 | end | 1.550 |
|1 |1 | start | 0.430 | |1 |1 | start | 0.430 |
|1 |1 | end | 1.420 | |1 |1 | end | 1.420 |
|2 |0 | start | 4.100 | |2 |0 | start | 4.100 |
|2 |0 | end | 4.512 | |2 |0 | end | 4.512 |
|2 |1 | start | 2.500 | |2 |1 | start | 2.500 |
|2 |1 | end | 5.000 | |2 |1 | end | 5.000 |
+------------+------------+---------------+-----------+ +------------+------------+---------------+-----------+

SELECT *
FROM Activity a0 JOIN Activity a3 ON
a0.machine_id = a3.machine_id AND a0.process_id = a3.process_id
AND a0.activity_type = "start" AND a3.activity_type = "end"

# Write your MySQL query statement below

SELECT a0.machine_id, round(avg(a3.timestamp - a0.timestamp), 3) AS processing_time
FROM Activity a0 JOIN Activity a3 ON
a0.machine_id = a3.machine_id AND a0.process_id = a3.process_id
AND a0.activity_type = "start" AND a3.activity_type = "end"
GROUP BY a0.machine_id;

GROUP BY Groups all unique data of that column suppose we have col of name and id and we grouped
by on the name of the table we will get unique name as grouped same names with differnet Id will be
considered same in group

DataBases_SQL Page 32
577. Employee Bonus
01 July 2024 17:02

# Write your MySQL query statement below

SELECT e.name, b.bonus
FROM Employee e LEFT JOIN Bonus b
ON e.empId = b.empId
WHERE bonus < 1000 OR bonus IS NULL;

DataBases_SQL Page 33
1280. Students and Examinations
03 July 2024 22:24

WITH master_data as
(SELECT *
FROM Students stu JOIN Subjects sub )

Select m1.student_id,
m1.student_name,m1.subject_name ,count(exam.subject_name) as
attended_exams
From master_data m1 LEFT JOIN Examinations exam
ON m1.student_id = exam.student_id and m1.subject_name =
exam.subject_name
GROUP BY 1,2,3 Order By 1,2;

WITH master_data as
(SELECT *
FROM Students stu JOIN Subjects sub )
SELECT *
FROM Students stu JOIN Subjects sub ; Select m1.student_id,
m1.student_name,m1.subject_name ,count(exam.subject_name) as attended_exams
From master_data m1 LEFT JOIN Examinations exam
ON m1.student_id = exam.student_id and m1.subject_name = exam.subject_name
GROUP BY 1,2,3 Order By 1,2;

DataBases_SQL Page 34
570. Managers with at Least 5 Direct Reports
03 July 2024 22:31

SELECT e1.id , e1.name, count(e2.id) as report_count

FROM Employee e1 JOIN Employee e2
ON e1.id = e2.managerId
GROUP BY 1,2;

# Write your MySQL query statement below

WITH report_data AS (
SELECT e1.id , e1.name, count(e2.id) as report_count
FROM Employee e1 JOIN Employee e2
ON e1.id = e2.managerId
GROUP BY 1,2
)
SELECT rd.name
FROM report_data rd
WHERE rd.report_count >= 5;

DataBases_SQL Page 35
DataBases_SQL Page 36
1211. Queries Quality and Percentage
24 July 2024 01:55

# Write your MySQL query statement below

SELECT query_name , ROUND(AVG(rating / position),2) as quality ,
ROUND((SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END)*100 / COUNT(*) ),2) as poor_query_percentage
FROM Queries WHERE query_name IS NOT NULL
GROUP BY 1;

DataBases_SQL Page 37
1193. Monthly Transactions I
24 July 2024 02:23

Getting this was the biggest challenge:

# Write your MySQL query statement below

SELECT SUBSTRING(trans_date ,1, 7) as month, country,
COUNT(*) as trans_count,
SUM(CASE WHEN state = "approved" THEN 1 ELSE 0 END) as approved_count,
SUM(amount) as trans_total_amount,
SUM(CASE WHEN state = "approved" THEN amount ELSE 0 END) as approved_total_amount
FROM Transactions
GROUP BY 1,2;

DataBases_SQL Page 38