Introducere in IT
Programming (SQL)
Introducere in IT 1
Curriculum
1. How an application comes to life?
● Who are the craftsmen involved?
● How does an application grow?
● When are we ready to ship?
2. Must-have skills for the journey
● How computers work?
● What are your basic tools?
● What are applications made of?
● How does information travel through Internet?
Introducere in IT 2
Curriculum
3. Can you become a developer?
● Start thinking and speaking like an IT geek!
● You call it a website - this is a perfect mix between HTML, CSS, Javascript
● Everything around us is an object - In machines’ world this is called OOP
● You like to keep your stuff well organized - So does an application using databases
4. … or what about being a tester?
● Apps crashing, monitors freezing, odd coloring screens, weird behavior… Are all these
happening only to me?
5. Show us your skills, we show you the path
● Are you amazed of what you’ve found so far? This was just a glimpse! We help you choose
the best path to follow: testing or developing applications
Introducere in IT – Chapter three 3
Chapter Three
Introducere in IT – Chapter three 4
Agenda
● Databases
● Structured Query Language (SQL)
● Data Definition Language (DDL)
● Data Manipulation Language (DML)
Introducere in IT – Chapter three 5
Database
Introducere in IT – Chapter three 6
Database
● Multilayer architecture
● Data access layer
● Database
Data access layer
Introducere in IT – Chapter three 7
File system Vs Database
File system Database
● Low Cost ● Control redundancy
● Data recovery ● Security
● No training ● Multiple user access
● No security ● Integrity constraints
● Backup and recover
Introducere in IT – Chapter three 8
Database
● Database entities
○ Table: collection of rows
○ Row (or record): actual data
○ Field (or cell): data member
○ Column: name of the fields
Introducere in IT – Chapter three 9
Database
● Database entities
Table Name
Column
Products
ProductID Name Brand Price Quantity
1 Galaxy S6 Samsung 3400 50
Record
2 Galaxy Alpha Samsung 1500 10
3 iPhone 6S Apple 3600 25
Field
Introducere in IT – Chapter three 10
Structured Query Language
(SQL)
Chapter two 11
SQL
● Structured Query Language (SQL)
○ Declarative language for query and manipulation of relational data
● SQL consists of:
○ Data Manipulation Language (DML)
■ SELECT, INSERT, UPDATE, DELETE
○ Data Definition Language (DDL)
■ CREATE, DROP, ALTER
○ Transactional Control Language (TCL)
■ COMMIT, ROLLBACK
Introducere in IT – Chapter three 12
SQL and CRUD
● CRUD for databases
Operation Data manipulation Data definition
Create INSERT CREATE
Read (Retrieve) SELECT -
Update (Modify) UPDATE ALTER
Delete (Destroy) DELETE DROP
Introducere in IT – Chapter three 13
Data Definition Language
(DDL)
Introducere in IT – Chapter three 14
Data Types
● Numeric
○ int (32-bit), bigint (64-bit)
○ float, double
○ decimal(scale, precision)– for money (precise) operations
● Strings
○ char(size) – fixed size string
○ varchar(size) – variable size string
○ text – text data block (unlimited size)
Introducere in IT – Chapter three 15
Data Types
● Binary data
○ binary – 0-255 binary strings
○ varbinary – 0-65535 binary strings
● Date and time
○ datetime – date and time
○ date – date and time (1-minute precision)
○ timestamp – automatically generated
Introducere in IT – Chapter three 16
Data Definition Language
● DDL commands for defining / editing objects
○ CREATE
■ CREATE DATABASE, CREATE TABLE, CREATE INDEX
○ ALTER
■ ALTER TABLE
○ DROP
■ DROP TABLE, DROP INDEX
Introducere in IT – Chapter three 17
Data Manipulation Language
(DML)
Introducere in IT – Chapter three 18
SQL Examples
●
SELECT Name, Price, Description FROM Products;
SELECT * FROM Brands WHERE Brand = 'Samsung';
INSERT INTO Products (Name, Price) VALUES('Galaxy S7',
1600);
UPDATE Products SET Warranty = 12 WHERE BrandID = 3;
DELETE FROM Products WHERE BuyDate = '1/1/2006';
Introducere in IT – Chapter three 19
SELECT Examples
● Selecting all columns from brands
SELECT * FROM Products;
ProductID Name BrandID
1 Galaxy Alpha 12
2 iPhone 6S 4
3 HTC ONE 273
● Selecting specific columns ProductID Name
SELECT 1 Galaxy Alpha
ProductID, Name 2 iPhone 6S
FROM Products;
3 HTC ONE
Introducere in IT – Chapter three 20
SELECT Examples
SELECT * FROM Products WHERE BrandID=22;
1 Galaxy Alpha 12
2 iPhone 6S 22
3 HTC ONE 273
4 iPhone H 22
5 iPhone Z 22
6 iPhone 7S 22
Introducere in IT – Chapter three 21
Arithmetic Operations
● Arithmetic operators are available: +, -, *, /
● Examples:
SELECT (7 + 3) * 2;
SELECT Name, Price, Price * 0.2 FROM Products;
Name Price (No column name)
Galaxy Alpha 1600,00 384,00
iPhone 6S 3500,00 840,00
HTC ONE 2000,00 480,00
Introducere in IT – Chapter three 22
Column Aliases
● Aliases rename a column heading
● Useful with calculations
● Immediately follows the column name
○ There is an optional AS keyword
● Double quotation marks if contains spaces
SELECT ProductID, Name, Price,
Price * 0.2 AS VAT FROM Products;
ProductID Name Price VAT
433 Galaxy Alpha 1600.00 384.00
221 iPhone 6S 3500.00 840.00
Introducere in IT – Chapter three 23
Concatenation
● Concatenates columns or strings to other columns
● Is represented function CONCAT(str1, str2, ...)
● Creates a resultant column that is a character expression
SELECT CONCAT(Name, ' / ', Brand) AS 'Full Name',
ProductID AS 'No.' FROM Products;
Full Name No.
Galaxy Alpha / Samsung 134
iPhone 6S / Apple 253
HTC ONE / HTC 321
Introducere in IT – Chapter three 24
Removing Duplicates
● The default display of queries is all rows, ProductID
7
including duplicate rows 7
SELECT ProductID 2
FROM Products; ...
● Eliminate duplicate rows by using the
DISTINCT keyword in the SELECT clause ProductID
SELECT 7
DISTINCT ProductID 2
FROM Products;
...
Introducere in IT – Chapter three 25
Limit Selected Rows
● Restrict the rows returned by using the WHERE clause:
SELECT Name, Brand FROM Products Name Brand
WHERE Brand = 'Samsung'; Note 5 Samsung
Galaxy S5 Samsung
Galaxy Alpha Samsung
● More examples: ... ...
SELECT Name, Price, Description FROM Products
WHERE ProductID = 123;
SELECT Name, Price FROM Products
WHERE Price <= 2000;
Introducere in IT – Chapter three 26
ORDER BY
● Sort rows with the ORDER BY clause
○ ASC: ascending order, default
○ DESC: descending order Name Price
Galaxy Alpha 1600
SELECT Name, Price
FROM Products HTC ONE 2000
ORDER BY Price; iPhone 6S 3500
SELECT Name, Price
Name Price
FROM Products
ORDER BY Price DESC; iPhone 6S 3500
HTC ONE 2000
Galaxy Alpha 1600
Introducere in IT – Chapter three 27
INSERT
● INSERT command
○ INSERT INTO <table> VALUES (<values>)
○ INSERT INTO <table>(<columns>) VALUES (<values>)
○ INSERT INTO <table> SELECT <values>
INSERT INTO Promotions
VALUES (101, 'Xperia Z1', 2200);
INSERT INTO Promotions(ProductID, Name, Price)
VALUES (101, 'Xperia Z1', 2200);
INSERT INTO Promotions(ProductID, Name, Price)
SELECT ProductID, Name, Price FROM Products;
Introducere in IT – Chapter three 28
UPDATE
● UPDATE command
○ UPDATE <table> SET <column = expression> WHERE
<condition>
○ Note: Don't forget the WHERE clause!
UPDATE Products SET Name = 'Galaxy A5'
WHERE ProductID = 1;
UPDATE Products
SET Price = Price * 1.10, Description = 'Smartphone...'
WHERE ProductID = 3;
Introducere in IT – Chapter three 29
DELETE
● Deleting rows from a table
○ DELETE FROM <table> WHERE <condition>
DELETE FROM Products WHERE ProductID = 1;
DELETE FROM Products WHERE Name LIKE '%5%';
○ Note: Don’t forget the WHERE clause!
● Delete all rows from a table at once
○ TRUNCATE TABLE <table>
TRUNCATE TABLE Users;
Introducere in IT – Chapter three 30
Multiple Tables
● Sometimes you need data from more than one table:
Name BrandID BrandID Brand
Galaxy Alpha 21 21 Samsung
iPhone 6S 23 22 HTC
HTC ONE 22 23 Apple
Name Brand
Galaxy Alpha Samsung
iPhone 6S Apple
HTC ONE HTC
Introducere in IT – Chapter three 31
JOIN
● To specify arbitrary conditions or specify columns to join, the ON clause is
used
○ Such JOIN is called also INNER JOIN
SELECT [Link], [Link], [Link], [Link], [Link]
FROM Products p
INNER JOIN Brands b ON [Link] = [Link];
ProductID Name BrandID BrandID Brand
1 Galaxy Alpha 7 7 Samsung
2 HTC ONE 4 4 HTC
3 iPhone 6S 1 1 Apple
Introducere in IT – Chapter three 32
Joins
Categories Products
Primary Key Foreign Key
Description CategoryName CategoryID CategoryID ProductID ProductName
Introducere in IT – Chapter three 33
Group Functions
● Group functions operate over sets of rows to give one single
result (per group)
ProductID Price
1 12500,00
2 13500,00
MAX(Price)
3 43300,00
43300,00
4 29800,00
5 25000,00
... ...
Introducere in IT – Chapter three 34
Group Functions
● COUNT(*) – count of the selected rows
● SUM(column) – sum of the values in given column from the
selected rows
● AVG(column) – average of the values in given column
● MAX(column) – the maximal value in given column
● MIN(column) – the minimal value in given column
Introducere in IT – Chapter three 35
Products
Creating Groups of Data
BrandID Price
12 10300
12 16800
12 16800 54200
BrandID TotalPrice
12 10300 12 54200
2 28800 2 83600
2 25000 16 185600
83600
2 29800 ... ...
16 125500
16 60100 185600
... Chapter four ...
Introducere in IT – Chapter three 36
GROUP BY
● Example of grouping data:
SELECT BrandID, SUM(Price) BrandID TotalPrice
as TotalPrice 12 72000
FROM Products 2 108600
GROUP BY BrandID; 16 185600
... ...
● The GROUP BY column is not necessary needed to be in the
SELECT list
Introducere in IT – Chapter three 37
Standard Functions
● Single-row functions
○ String functions
○ Mathematical functions
○ Date functions
○ Conversion functions
● Multiple-row functions
○ Aggregate functions
Introducere in IT – Chapter three 38
References
• [Link]
• [Link]
Introducere in IT – Chapter three 39