SQL on Linux and its uses and application.pdf

SQL (Structured Query Language) in Linux is a powerful tool for managing and manipulating relational databases. SQL allows users to query, update, insert, and delete data from databases, as well as manage database structures, such as tables, views, indexes, and more. SQL is supported on various database systems, including MySQL, PostgreSQL, MariaDB, and SQLite, which can be easily installed and run on Linux systems.

### SQL and Linux Integration
On Linux, SQL databases are often used in server environments to manage data for web applications, enterprise systems, and data analysis tasks. Many popular database management systems (DBMS) such as MySQL, MariaDB, and PostgreSQL come pre-packaged in Linux distributions or can be installed via package managers like `apt` (for Debian-based distributions) or `yum` (for Red Hat-based distributions).

#### Installation
To install MySQL or PostgreSQL on Linux, users can run:
- For MySQL: `sudo apt-get install mysql-server` (Debian-based) or `sudo yum install mysql-server` (Red Hat-based)
- For PostgreSQL: `sudo apt-get install postgresql postgresql-contrib`

After installation, database services can be started using system commands like `sudo systemctl start mysql` or `sudo systemctl start postgresql`.

### Working with SQL on Linux
Once installed, users can interact with the databases via terminal using SQL commands. For example:
- To log in to MySQL: `mysql -u root -p`
- To log in to PostgreSQL: `psql -U postgres`

SQL commands can be used to:
- **Create a database:** `CREATE DATABASE dbname;`
- **Create a table:** `CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), age INT);`
- **Insert data:** `INSERT INTO users (id, name, age) VALUES (1, 'John', 25);`
- **Query data:** `SELECT * FROM users;`

### Administration and Security
Linux provides strong security features for SQL databases. Permissions can be granted to specific users with commands like `GRANT` and `REVOKE`. Databases can be protected by controlling access to users and encrypting sensitive data. Linux-based tools like `cron` can be used for scheduled backups, while firewall settings help secure databases from unauthorized access.

### Conclusion
SQL on Linux is integral to managing data efficiently in modern computing environments. With its stability, security, and powerful command-line tools, Linux provides an excellent platform for running SQL databases for a wide range of applications.
In addition to managing databases directly via the command line, Linux users can also take advantage of graphical user interfaces (GUIs) like **phpMyAdmin**, **pgAdmin**, or **DBeaver**, which make interacting with SQL databases more intuitive by providing visual tools for creating tables, running queries, and managing data. These tools can be easily installed on Linux systems and are often used in tandem with command-line operations.

Linux also supports integration with various programming languages, such as Python, PHP, and Java, allowing developers t

1. ALRIGHT!

2. SETUP AND START WORING ON LINUX
MYSQL TUTORIAL

3. MySql Installation
Securing MySQL
Testing connectivity with MySQL
Basic necessary queries
Creating Database
Creating Table
Inserting data into the table
Reading data from Table
Update data into a table
Topics

4. Step 1: Installation
#yum install mysql-server.x86_64

5. Start/Enable MySQL
#sudo systemctl status mysqld.service
#sudo systemctl start mysqld.service
#sudo systemctl enable/disable mysqld.service

6. Secure the authentication process by
setting password for root
Step 2: Changing default options
#sudo mysql_secure_installation

7. Step 3: Testing Connection
You can verify your installation with the
mysqladmin tool
#mysqladmin -u root -p version
To connect and start using SQL Queries
#mysql -u root -p

8. Some Important Queries in starting
SHOW DATABASES;
SHOW TABLES;
USE <database_name>

9. Creating a new Database
CREATE DATABASE <db_name>;

10. CREATE
READ
UPDATE
DELETE
CRUD

12. Creating a new Table
CREATE TABLE students (
id INT,
name VARCHAR(100)
)
CREATE TABLE students (name VARCHAR(100), age INT)

13. Checking yout table
DESC TABLE

14. Adding data into a Table
INSERT INTO students(id, name)
VALUES (101, "Rahul")
INSERT INTO students VALUES (101, "Rahul")

15. Reading data from a Table
SELECT * FROM <table_name>
SELECT <column_name> from students

16. Modify/Update data from a Table
UPDATE students
SET contact=12345
WHERE name='Raju';

17. DELETE data from a Table
DELETE FROM students
WHERE name='Raju';

18. Deleting a Database or Table
DROP DATABASE <db_name>;
DROP TABLE <table_name>;

19. THANKS FOR WATCHING

20. Databases

21. List All Existing Database
SHOW DATABASES;

22. Creating a new Database
CREATE DATABASE <db_name>;

23. Working with a Database
USE <db_name>;

24. Deleting a Database
DROP DATABASE <db_name>;

25. Tables

26. Table
A table is a collection of related data
held in a table format within a database.

27. Creating a new Table
CREATE TABLE students (
id INT,
name VARCHAR(100)
)
CREATE TABLE students (name VARCHAR(100), age INT)

28. Checking your table
DESC TABLE

29. Adding data into a Table
INSERT INTO students(id, name)
VALUES (101, "Rahul")
INSERT INTO students VALUES (101, "Rahul")

30. Reading data from a Table
SELECT * FROM <table_name>
SELECT <column_name> from students

31. Modify/Update data from a Table
UPDATE students
SET contact=12345
WHERE name='Raju';

32. DELETE data from a Table
DELETE FROM students
WHERE name='Raju';

33. DataTypes

34. DataTypes
An attribute that specifies the type of data in a
column of our database - table.

36. Most widely used are
Numeric - INT DOUBLE FLOAT DECIMAL
String - VARCHAR
Date

37. NOT NULL

39. DEFAULT Value

40. CREATE TABLE employee(
name VARCHAR(100),
acc_type VARCHAR(50) DEFAULT 'savings'
);

41. Primary Key

42. Primary Key
The PRIMARY KEY constraint uniquely identifies each
record in a table.
Primary keys must contain UNIQUE values, and cannot
contain NULL values.
A table can have only ONE primary key.

45. AUTO_INCREMENT

47. ALIAS

48. WHERE

51. Exercise - 1

52. Task
Create a database - bank_db
Create a table - employees
emp_id
name
desig
dept
emp_id column should not allow duplicate and null values
name column should not contain null value
desig column should have default value as 'Probation'
Value of emp_id column should auto increment

53. Task
Insert the following data

54. Task
Display the data

55. Exercise - 2

57. Task
Display the data

58. Task
Update the following data

59. Task
DELETE the following data

60. String Functions

61. CONCAT
CONCAT(first_col, sec_col)
CONCAT(first_word, sec_word, ...)

62. CONCAT_WS
CONCAT_WS('-', fname, lname)

63. SUBSTRING
SELECT SUBSTRING('Hey Buddy', 1, 4);

64. SUBSTRING
SELECT SUBSTRING('Hey Buddy', 1, 4);
1 4
Result: Hey

65. REPLACE

66. REPLACE
Hey Buddy
Hey Hello
Hello Buddy

67. REPLACE(str, from_str, to_str)
REPLACE('Hey Buddy', 'Hey', 'Hello')

68. REVERSE
SELECT REVERSE('Hello World');

69. CHAR_LENGTH
Select CHAR_LENGTH('Hello World');

70. UPPER & LOWER
SELECT UPPER('Hello World');
SELECT LOWER('Hello World');

71. Other Functions

72. SELECT INSERT('Hello Wassup', 6, 0, 'Raju');
SELECT LEFT('Abcdefghij', 3);
SELECT RIGHT('Abcdefghij', 4);
SELECT REPEAT('o', 5);
SELECT TRIM(' Alright! ');

73. Exercise - 3

75. Task 1:
101:Raju:Manager:Loan
Task2:
101:Raju Rastogi:Manager:Loan

76. Task3
101:Raju:MANAGER:Loan
Task4
L101 Raju
C102 Sham

77. DISTINCT
SELECT DISTINCT fname FROM employees;

78. ORDER BY
SELECT * FROM employees ORDER BY fname;

79. LIKE
Select * FROM employees
WHERE dept LIKE "%Acc%";

80. LIMIT
SELECT * FROM employees LIMIT 3;

81. ALTER TABLE employees
ADD COLUMN
salary INT NOT NULL
DEFAULT 25000;

82. GROUP BY

84. Loan Cash Account
IT
Deposit

85. SELECT dept FROM employees GROUP BY dept;
SELECT dept, COUNT(fname) FROM employees GROUP
BY dept;

86. COUNT
SELECT COUNT(*) FROM employees;

87. MAX & MIN
SELECT MAX(age) FROM employees;
SELECT MIN(age) FROM employees;

88. SELECT emp_id, fname, salary FROM employees
WHERE
salary = (SELECT MAX(salary) FROM employees);

89. SUM & AVG
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;

90. Exercise - 4
DISTINCT, ORDER BY, LIKE and LIMIT

92. 1: Find Different type of departments in database?
2: Display records with High-low salary
3: How to see only top 3 records from a table?
4: Show records where first name start with letter 'A'
5: Show records where length of the lname is 4 characters

93. 1
2
3

94. 4
5

95. Exercise - 5
COUNT, GROUP BY, MIN, MAX and SUM and AVG

97. 1: Find Total no. of employees in database?
2: Find no. of employees in each department.
3: Find lowest salary paying
4: Find highest salary paying
5: Find total salary paying in Loan department?
6: Average salary paying in each department

99. DATAYPES
CHAR vs VARCHAR

100. DATAYPES
The CHAR and VARCHAR types are similar,
but differ in the way they are stored
and retrieved.

101. DATAYPES
VARCHAR(50)
The length can be specified as a value
from 0 to 65,535.

102. DATAYPES
CHAR is fixed length
CHAR(5) --> Only 5 character allowed
The length can be any value from 0 to
255.

103. DATAYPES
When CHAR values are stored, they are right-
padded with spaces to the specified length.
When CHAR values are retrieved, trailing spaces
are removed unless the PAD_CHAR_TO_FULL_LENGTH
SQL mode is enabled.

104. CHAR(4)
If you try to insert value 'AB'
MySQL will store the value as 'AB '

106. CHAR can be used in case of fixed length values
like
Country code: US, IN, AU

107. DATAYPES
DECIMAL

108. DATAYPES
So far we have only used and seen
INT Datatype...

109. DATAYPES
What will happen when we store
values like 15.35?

110. DATAYPES
DECIMAL(6,3)

The maximum number of digits for DECIMAL is 65

111. DATAYPES
DECIMAL(5,2)
Total digit
Digits after decimal

112. DATAYPES
Example: 155.38
119.12
28.15
1150.15

113. DATAYPES
FLOAT, DOUBLE

114. DATAYPES
FLOAT, DOUBLE
MySQL uses four bytes for single-precision
values and eight bytes for double-precision

115. DATAYPES
FLOAT, DOUBLE
Float - upto ~7 digits, takes 4 bytes of memory
Double - upto ~15 digits, takes 8 bytes of memory

117. DATAYPES
DATE, TIME, DATETIME

118. DATAYPES
DATE, TIME, DATETIME
DATE
yyyy-mm-dd format

119. DATAYPES
DATE, TIME, DATETIME
TIME
HH:MM:SS format

120. DATAYPES
DATE, TIME, DATETIME
DATETIME
'yyyy-mm-dd HH:MM:SS' format

121. DATE TIME Functions
CURDATE, CURTIME, NOW

122. DATE TIME Functions
CURDATE() - yyyy-mm-dd
CURTIME() - hh:mm:ss
NOW() - yyyy-mm-dd hh:mm:ss

123. DAYNAME,
DAYOFMONTH,DAYOFWEEK
DATE TIME Functions

126. DATE Formatting

127. DATE Formatting
Tue Mar 28th
21st Tue at 21:20:28
2023/04/18
Suppose we need to get date in a given format like

128. DATE Formatting
DATE_FORMAT()
DATE_FORMAT(now(), '%D %a at %T')
Result: 21st Tue at 21:20:28
DATE_FORMAT(now(), '%m/%d/%y')
Result: 04/16/23

129. DATE Formatting
DATE_FORMAT()
DATE_FORMAT(now(), '%H:%i')
Result: 20:34
DATE_FORMAT(dob, '%r')
Result: 08:35:48 PM

130. DATE MAth

132. DATEDIFF(expr1, expr2)
DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)

133. DATE_ADD('2023-05-01',INTERVAL 1 DAY)
DATE_ADD('2023-05-01',INTERVAL 1 YEAR)
DATE_SUB('2023-05-01',INTERVAL 1 MONTH)

134. TIMEDIFF(expr1, expr2)
TIMEDIFF('20:00:00', '18:00:00')

135. DEFAULT &
ON UPDATE TIMESTAMP

137. CREATE TABLE blogs (
text VARCHAR(150),
created_at DATETIME default CURRENT_TIMESTAMP,
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
);

138. Exercise - 6

139. Exercise - 6
Print the current time (HH:MM:SS)
Print the current date (yyyy-mm-dd)
Print current day of the week (Monday, Tuesday...)
What is the use case of CHAR datatype?
Which datatype can be used to store value 123.456?
Display date in format
dd:mm:yyyy
April 22nd at 22:00:00

140. Exercise - 6
Solution

141. Exercise - 6
Print the current time (HH:MM:SS)
Print the current date (yyyy-mm-dd)
Print current day of the week (Monday, Tuesday...)
What is the use case of CHAR datatype?
Which datatype can be used to store value 123.456?
Display date in format
dd:mm:yyyy
April 22nd at 22:00:00

142. Exercise - 6
SELECT CURTIME();
SELECT CURDATE();
SELECT DAYNAME(NOW());
What is the use case of CHAR datatype?
Which datatype can be used to store value 123.456?
Display date in format
dd:mm:yyyy - date_format(now(), '%d:%m:%Y')
April 22nd at 22:00:00 - date_format(now(), '%M %D at %T')

143. Operators

144. Relational Operators

145. Find employees whose salary is more than
65000

146. =
=
We have relational
operators

147. SELECT * FROM employees
WHERE salary > 65000;

148. Logical Operators

149. AND
OR

150. ANDCondition 1 Condition 2
When both the conditions are true

151. ANDsalary=25000 dept=Loan

152. ORCondition 1 Condition 2
When either of the condition is true

153. ORsalary=65000 desig='Lead'

154. IN, NOT IN

155. Account
Cash
Loan
Find employees From following department

156. SELECT * FROM employees
WHERE dept = 'Account'
OR dept = 'Cash'
OR dept = 'Loan';

157. SELECT * FROM employees
WHERE dept IN ('Account', 'Cash', 'Loan');

158. BETWEEN

159. Find employees whose salary is more than
40000 and Less than 65000
>40000
<65000

160. SELECT * FROM employees
WHERE
salary >=40000 AND salary <=65000;

161. SELECT * FROM employees
WHERE
salary BETWEEN 40000 AND 65000;

162. CASE

167. IS NULL

168. IS NULL
SELECT * FROM employees
WHERE fname IS NULL;

169. NOT LIKE

170. IS NULL
SELECT * FROM employees
WHERE fname NOT LIKE 'A%';

171. Exercise - 7

173. Find employees whose salary are between
30000 to 40000
Find employees whose first name start with
Find employee whose salary=25000 and
department should be 'Cash'
Find employees from following designation
Manager, Lead and Associate
'R' or 'S'

175. Exercise - 7
Solution

178. UNIQUE
CONSTRAINT

179. What if we need to store the
phone numbers in a column.

181. CHECK
CONSTRAINT

182. We want to make sure phone no. is
atleast 10 digits...

184. NAMED CONSTRAINT

185. ALTERING
Tables

186. How to add or remove a column?

189. How to rename a column or table name?

190. How to rename a column?

191. How to rename a table name?

192. How to modify a column?
Ex: Changing datatype
or adding Default values etc

193. How to add DEFAULT value to a column?

194. How to change column name and set NOT
NULL?

195. How to Add or Drop Constraints?

196. How to Check Existing Constraints?

198. Relationship

199. RElATIONSHIP

200. Simple data and table...

201. Real World

202. Data is not that simple...

203. Employees

204. Employees
Salary Attendance
requests task
offices

205. Types of Relationship
One to One
One to Many
Many to Many

206. 1 : 1

207. Employees
Employee
Details

208. 1 : MANY

209. Employees
Employee Task

210. Many : Many

211. Books Authors

212. Author A
Author B
Book A
Book B
Book C
Book D

214. Let's Understand a Use-Case of
1:Many

216. Suppose we need to store the following data
customer name
customer email
order date
order price

218. Customers Orders
cust_id
cust_name
cust_email
order_id
order_date
order_amount

219. Customers Orders
cust_id
cust_name
cust_email
order_id
order_date
order_amount
cust_id

220. Customers
Orders

221. Foreign Key

222. Customers
Orders

223. Customers
Orders
Primary Key
Foreign Key
Primary Key

224. Let's work practically with
Foreign Key..

227. JOINS

228. JOIN operation is used to combine rows
from two or more tables based on a related
column between them.

229. Customers
Orders
Primary Key
Foreign Key
Primary Key

230. Types of Join
Cross Join
Inner Join
Left Join
Right Join

231. Cross Join
Every row from one table is combined with
every row from another table.

232. Inner Join
Returns only the rows where there is a match
between the specified columns in both the
left (or first) and right (or second) tables.

235. Inner Join with Group By

236. Left Join
Returns all rows from the left (or first) table
and the matching rows from the right (or
second) table.

239. Right Join
Returns all rows from the right (or second)
table and the matching rows from the left (or
first) table.

242. CASCADE ON DELETE

243. Customers
Orders
Primary Key
Foreign Key
Primary Key

245. Exercise - 8

246. Authors
author_id
author_name
Books
book_id
title
ratings
au_id

252. Exercise - 8
Solution

253. Many : Many

254. Let's Understand a Use-Case of
Many : Many

255. Students
Courses

256. Student A
Course A
Course B
Course C

257. Student A
Course A
Student B
Student C

258. id
student_name
students
id
course_name
fees
courses

259. id
student_name
students
id
course_name
fees
courses
student_id
course_id
student_course

260. id
student_name
students
id
course_name
fees
courses
student_id
course_id
student_course

262. Exercise - 9
Solution

263. Print No. of students for each course.

264. Print No. of courses taken by each students

265. Total FEES Paid by Each Student

266. VIEWS

268. HAVING Clause

270. GROUP BY ROLLUP

272. STORED Routine

273. STORED Routine
An SQL statement or a set of SQL
Statement that can be stored on
database server which can be call no. of
times.

274. Types of STORED Routine
STORED Procedure
User defined Functions

275. STORED Procedure

276. STORED PROCEDURE
These are routines that contain a series of
SQL statements and procedural logic.
Often used for performing actions like data
modification, transaction control, and
executing sequences of statements.

280. USER DEFINED FUNCTIONS

282. WINDOW FUNCTIONS

283. Introduced in version 8.0.
Window functions, also known as analytic
functions allow you to perform calculations
across a set of rows related to the current row.
Defined by an OVER() clause.

284. ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()