DB administration. Queries.

Overview

Teaching: 90 min

Questions

• How do you make sure your data stored, can be accessed only by authorized persons?

• How you query your database to obtain data required for your analytics?

Objectives

• Understanding the options of altering a db

• Introduction to database security

• Understanding datatypes

• Present examples and exercise querying databases

Keywords

#ALTERING DB

#SECURITY

#DATA TYPES

#LOGICAL OPERATORS

#FILTERING

Table of Content

Chapter’s database
Altering your first database
Users and privileges
More advanced selects
Data types
Comparison Operators
Filtering with VARCHAR
Filtering with INT
Filtering with DATE
Homework

Chapter’s database

No need to load new data, in this chapter we will use only the birdstrikes table loaded in the last chapter:

Altering your first database

Copy table

CREATE TABLE new_birdstrikes LIKE birdstrikes;
SHOW TABLES;
DESCRIBE new_birdstrikes;
SELECT * FROM new_birdstrikes;

Delete table

DROP TABLE IF EXISTS new_birdstrikes;

Create table

Exercise1

Based on the previous chapter, create a table called “employee” with two columns: “id” and “employee_name”. NULL values should not be accepted for these 2 columns.

Insert new rows (records)

Insert lines in employee table one by one

INSERT INTO employee (id,employee_name) VALUES(1,'Student1');
INSERT INTO employee (id,employee_name) VALUES(2,'Student2');
INSERT INTO employee (id,employee_name) VALUES(3,'Student3');

Let’s check the results

SELECT * FROM employee;

What happens if you try this (and why)?

INSERT INTO employee (id,employee_name) VALUES(3,'Student4');

Updating rows

UPDATE employee SET employee_name='Arnold Schwarzenegger' WHERE id = '1';
UPDATE employee SET employee_name='The Other Arnold' WHERE id = '2';

Let’s check the results

SELECT * FROM employee;

Deleting rows

Deleting some records

DELETE FROM employee WHERE id = 3;

Let’s check the results

SELECT * FROM employee

Deleting all rows

TRUNCATE employee;

Let’s check the results

SELECT * FROM employee;

Users and privileges

Creating new user

CREATE USER 'laszlosallo'@'%' IDENTIFIED BY 'laszlosallo1';

Giving full rights for table employee

GRANT ALL ON birdstrikes.employee TO 'laszlosallo'@'%';

Giving rights to see one column of birdstrikes

GRANT SELECT (state) ON birdstrikes.birdstrikes TO 'laszlosallo'@'%';

Deleting user

DROP USER 'laszlosallo'@'%';

More advanced selects

New column

Create a new column

SELECT *, speed/2 FROM birdstrikes;

Aliasing

SELECT *, speed/2 AS halfspeed FROM birdstrikes;

Using Limit

List the first 10 records

SELECT * FROM birdstrikes LIMIT 10;

List the first 1 record, after the the first 10

SELECT * FROM birdstrikes LIMIT 10,1;

Exercise2

What state figures in the 145th line of our database?

Ordering data

Order by a field

SELECT state, cost FROM birdstrikes ORDER BY cost;

Order by a multiple fields

SELECT state, cost FROM birdstrikes ORDER BY state, cost ASC;

Reverse ordering

SELECT state, cost FROM birdstrikes ORDER BY cost DESC;

Exercise3

What is flight_date of the latest birstrike in this database?

Unique values

Of a column

SELECT DISTINCT damage FROM birdstrikes;

Unique pairs

SELECT DISTINCT airline, damage FROM birdstrikes;

Exercise4

What was the cost of the 50th most expensive damage?

Filtering

Select the lines where states is Alabama

SELECT * FROM birdstrikes WHERE state = 'Alabama';

Data types

Comparison Operators

Filtering with VARCHAR

NOT EQUAL

Select the lines where states is not Alabama

SELECT * FROM birdstrikes WHERE state != 'Alabama'

States starting with ‘A’

LIKE

SELECT DISTINCT state FROM birdstrikes WHERE state LIKE 'A%';

Note the case (in)sensitivity

SELECT DISTINCT state FROM birdstrikes WHERE state LIKE 'a%';

States starting with ‘ala’

SELECT DISTINCT state FROM birdstrikes WHERE state LIKE 'ala%';

States starting with ‘North ‘ followed by any character, followed by an ‘a’, followed by anything

SELECT DISTINCT state FROM birdstrikes WHERE state LIKE 'North _a%';

States not starting with ‘A’

SELECT DISTINCT state FROM birdstrikes WHERE state NOT LIKE 'a%' ORDER BY state;

Logical operators

Filter by multiple conditions

SELECT * FROM birdstrikes WHERE state = 'Alabama' AND bird_size = 'Small';
SELECT * FROM birdstrikes WHERE state = 'Alabama' OR state = 'Missouri';

IS NOT NULL

Filtering out nulls and empty strings

SELECT DISTINCT state FROM birdstrikes WHERE state IS NOT NULL AND state != '' ORDER BY state;

IN

What if I need ‘Alabama’, ‘Missouri’,’New York’,’Alaska’? Should we concatenate 4 AND filters?

SELECT * FROM birdstrikes WHERE state IN ('Alabama', 'Missouri','New York','Alaska');

LENGTH

Listing states with 5 characters

SELECT DISTINCT(state) FROM birdstrikes WHERE LENGTH(state) = 5;

Filtering with INT

Speed equals 350

SELECT * FROM birdstrikes WHERE speed = 350;

Speed equal or more than 25000

SELECT * FROM birdstrikes WHERE speed >= 10000;

ROUND, SQRT

SELECT ROUND(SQRT(speed/2) * 10) AS synthetic_speed FROM birdstrikes;

BETWEEN

SELECT * FROM birdstrikes where cost BETWEEN 20 AND 40;

Exercise5

What state figures in the 2nd record, if you filter out all records which have no state and no bird_size specified?

Filtering with DATE

Date is “2000-01-02”

SELECT * FROM birdstrikes WHERE flight_date = "2000-01-02";

All entries where flight_date is between “2000-01-01” AND “2000-01-03”

SELECT * FROM birdstrikes WHERE flight_date >= '2000-01-01' AND flight_date <= '2000-01-03';

BETWEEN

SELECT * FROM birdstrikes where flight_date BETWEEN "2000-01-01" AND "2000-01-03";

Exercise6

How many days elapsed between the current date and the flights happening in week 7, for incidents from Colorado? (Hint: use NOW, DATEDIFF, WEEKOFYEAR)

Homework 2

• Upload the solution of exercise 1-6 to your GitHub repo in a folder called HW2
• Make sure to submit both the SQL statements and answers to the questions
• The required data format for submission is a .sql file
• Submit GitHub repo link to moodle when you are ready