Embedded Files
What Join types do you know
inner (default), RIGHT, LEFT, FULL Кросс(очень редко испол-ся)
________________________
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id=d.department_id
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id=d.department_id
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
where e.last_name LIKE 'G%'
SELECT e.first_name, e.last_name, d.department_name
FROM departments d
RIGHT JOIN employees e
ON e.department_id=d.department_id
where e.last_name LIKE 'G%'
__________
1) SELECT e.name, e.age, e.salary, d.name одинаковый результат в 1)2)3)
from emp e, dept d
JOIN dept d emp=
where e.dep_id=d.id
AND e.salary>60000, e.age>33 AND d.name='Sales'
2)SELECT e.name, e.age, e.salary, d.name
from emp e
INNER JOIN dept d ON e.dep_id=d.id
where e.salary>60000, e.age>33 AND d.name='Sales'
3)SELECT name, age, salary
FROM dmp
WHERE dep_id=(SELECT id from dept where name='Sales')
AND e.salary>60000, e.age>33
__________________
http://34.201.11.175:8080/apex/
https://oracleplsql.ru/update.html
SELECT
DELETE - all records, can be undone
TRUNCATE TABLE table_name - it cannot be undone
Interview Questions
1. What is a Database (dB)?
2. What is a Relational database? Advantages?
3. What is a Schema? What are the schema commands?
4. What is a Table? Can we have an empty table?
5. What is Field? What ‘part’ of the table?
6. What is Record? What ‘part’ of the table?
7. What is Query?
8. What is Report? How much information can you get in one report?
9. What is DBMS? Name a few popular databases.
10. What is a PRIMARY KEY (PK) / FOREIGN KEY (FK)?
1. What is a Database (dB)?
Database is an organized collection of data. (представляет собой организованный сбор данных)
It allows us to control data accuracy, data manipulation (sort, retrieve (извлекать), update…);
and provide Data Security.
2. What is a Relational database? Advantages?
Relational databases use tables to store information. It allows you to easily find specific information, sort based on any field, and generate reports that contain only certain fields from each record.
3. What is a Schema? What are the schema commands?
Logical container for database objects (tables, views, triggers) that user creates.
4. What is a Table? Can we have an empty table?
Table is a set of data elements (values) that is organized using columns (fields) and rows (records).Table has a specified number of columns, but can have any number of rows.
-
5. What is Field? What ‘part’ of the table?
Field is database storage simplest unit.
-
6. What is Record? What ‘part’ of the table?
Record is a row and it represents a single structured data set in a table.
-
7. What is Query?
Query is your request to the database to retrieve information.
8. What is Report? How much information can you get in one report?
If the query is a question... ...then the report is its answer. Reports can be tailored to the needs of the data user, making the information they extract much more useful.
-
9. What is DBMS? Name a few popular databases.
DBMS (Database Management System) is software that controls the organization, storage, retrieval, security and integrity of data in a database. DBMS accepts requests from the application and instructs the operating system to transfer the appropriate data.
DBMS vendors are: Oracle, IBM, Microsoft, and Sybase.
10. What is a PRIMARY KEY (PK) / FOREIGN KEY (FK)?
PRIMARY KEY (PK) is a unique identifier of every record in a table. Never can be NULL!
A foreign key (FK) is a column (or combination of columns) that is used to establish a relationship between the tables;
Foreign key is usually not unique (one-to-many relation) and shall always point to a primary key.
PK never can be NULL! FK - can be NULL
PK purpose - to avoid duplicate, unique identifier to make every record in the table unique.
FK purpose - to establish a relationship between tables.
для безопасности - пароль не хранится просто так в БД , будет храниться в зашифрованном виде. (инкрипшн код - хэш)
Interview Questions
1. What is a database NORMALIZATION?
2. What is SQL stands for? What does it do?
3. Name a few Schema commands; Describe.
4. How to add a record to the table?
5. How to change one field in the table?
6. How to change table column?
7. What is the difference between DROP, DELETE and TRUNCATE?
1. What is a database NORMALIZATION?
NORMALIZATION is the process of dividing large tables into smaller tables and defining relationships between them.
НОРМАЛИЗАЦИЯ — это процесс разделения больших таблиц на более мелкие и определения отношений между ними.
2. What is SQL stands for? What does it do?
SQL is a database computer language designed for the retrieval and management of data in relational databases, database schema creation and modification, and database object access control management.
SQL — это компьютерный язык баз данных, предназначенный для поиска и управления данными в реляционных базах данных, создания и изменения схемы базы данных, а также управления доступом к объектам базы данных.
3. Name a few Schema commands; Describe.
4. How to add a record to the table?
5. How to change one field in the table?
6. How to change table column?
7. What is the difference between DROP, DELETE and TRUNCATE?
CREATE TABLE su3_class (ID INT PRIMARY KEY, fname VARCHAR(255), lname VARCHAR(255), sex VARCHAR(1))
DESCRIBE su3_class
INSERT INTO su3_class VALUES ((SELECT MAX(id) +1 FROM su3_class), 'Katya', 'Belova', 'M')
SELECT * FROM su3_class
INSERT INTO su3_class VALUES (4, 'Angelina', 'Jolie', 'F')
SELECT MAX(id) +1 FROM su3_class
ALTER TABLE su3_class RENAME COLUMN sex TO gender
ALTER TABLE su3_class ADD address VARCHAR(255)
UPDATE su3_class SET address = '8 Vancuver, 8' WHERE id=5
UPDATE su3_class SET lname = 'Popov' WHERE id = 2
UPDATE su3_class SET address = NULL WHERE id=5
DELETE FROM su3_class WHERE id=4
TRUNCATE TABLE
______________________________ HOME WORK____________________________
1) Create table “City_123” (Id, City_name, Population, Good_attraction) and verify the result;
CREATE TABLE City_123 (Id INT PRIMARY KEY, City_name CHAR(50), Population NUMERIC(5), Good_attraction NUMERIC(1)) - CREATE TABLE
2) Drop table “City_123” and verify the result;
3) Recreate table “City_123” and verify the result;
DESC City_Info_123
4) Change table name “City_123” to “City_Info_123”
ALTER TABLE City_123 RENAME TO City_Info_123 - RENAME TABLE
5) Add “State_Capital” and “State” columns;
ALTER TABLE City_Info_123 ADD (State_Capital VARCHAR(30), State CHAR(2)) - ADD COLUMNS INTO TABLE
6) Change “Good_Attraction” type from numeric to alphanumeric;
ALTER TABLE City_Info_123 MODIFY Good_attraction CHAR - CHANGE TYPE
7) Change “City_Name” column length to up to 30 characters;
ALTER TABLE City_Info_123 MODIFY City_name VARCHAR(30)
8) Remove “Population” column;
ALTER TABLE City_Info_123 DROP COLUMN Population - DELETE COLUMN
9) Add Column “Schools”;
ALTER TABLE City_Info_123 ADD (Schools NUMERIC(3))
SELECT * FROM City_Info_123
10) City_Name, State_Capital, State can not be null;
12) Add the following records to “City_Info_123”:
INSERT INTO City_Info_123 VALUES (1, 'San Francisco', NULL, 'Sacramento', 'CA', 457) - ADD VALUES INTO THE TABLE
13) To confirm run: select * from city_info_123
14) Change all “Good_Attraction” to “N”;
UPDATE City_Info_123 -UPDATE VALUES IN TABLE
SET Good_attraction='N'. - Column_name='value what we want to update'
WHERE Id=1
14) Change all California cities “Good_Attraction” to “Y”;
UPDATE City_Info_123
SET Good_attraction='N'
WHERE Id>1
16)
UPDATE City_Info_123
SET Good_attraction='Y'
WHERE Id<=7
16) Change NY “Good_Attraction” to “Y”;
UPDATE City_Info_123
SET Good_attraction='Y'
WHERE City_name='New York'
18) Add Record for “Saratoga” as specified
INSERT INTO City_Info_123 VALUES (12, 'Sarasota', NULL, 'Sacramento', 'CA', 568)
19) Change “Good_Attraction” to “N” and “Schoold” to 5 for the Saratoga city;
UPDATE City_Info_123
SET Good_attraction='N',
Schools =5
WHERE City_name='Sarasota'
Class #3
DIFFERENCE
DDL works with table structure/architect.
DML works with date in these tables
_________________________
SELECT * FROM City_Info_123
DESCRIBE employees
DESCRIBE departments
SELECT * FROM employees
SELECT * FROM departments
SELECT DISTINCT department_id FROM employees
SELECT department_id FROM employees
SELECT department_id, department_name FROM departments
SELECT location_id FROM employees
SELECT DISTINCT department_id, location_id FROM departments
SELECT first_name, last_name, salary, department_id FROM employees WHERE department_id>=100
SELECT first_name, last_name, salary, department_id FROM employees WHERE NOT department_id>=100
SELECT first_name, last_name, salary, department_id FROM employees WHERE department_id=100 OR department_id=300
SELECT employee_id, first_name, last_name, salary, department_id FROM employees
WHERE department_id=80 AND salary>1000
AND employee_id>100 AND employee_id<150
SELECT first_name, last_name, salary, department_id FROM employees
WHERE (department_id=30 OR department_id=90) AND
last_name= 'King'
SELECT first_name, last_name, salary, department_id FROM employees
WHERE (department_id=30 OR department_id=90) OR
last_name= 'King'
SELECT first_name, last_name, salary, department_id FROM employees
WHERE department_id IN (50, 60, 90, 100)
SELECT first_name, last_name, salary, department_id FROM employees
WHERE salary BETWEEN 12000 AND 15000
SELECT first_name, last_name, salary, department_id FROM employees
WHERE first_name NOT LIKE '%a%'
SELECT first_name, last_name, salary, department_id FROM employees
WHERE department_id IS NULL
SELECT first_name, last_name, salary, department_id FROM employees
WHERE department_id = 80 ORDER BY last_name ASC
SELECT first_name, last_name, salary, department_id FROM employees
WHERE department_id = 80 ORDER BY last_name DESC
HW 3
1) Get the list of names and department Ids of everybody, reporting to manager (ID=103), or working at the Department 80, 60, or 90; Sort retrieved names within the department, having the largest department on top;
select first_name, last_name, department_id, manager_id from employees where manager_id=103 or department_id in (80,60,90)
select first_name, last_name, department_id, manager_id from employees where manager_id=103 or department_id in (80,60,90) order by department_id desc
2) Find out how much Luis Popp and David Austin are making a month?
select first_name, last_name, salary from employees where (first_name='Luis' and last_name='Popp') or (first_name='David' and last_name='Austin')
3) Find Ki Gee phone number
select phone_number from employees where (first_name='Ki' and last_name= 'Gee')
4) Provide all information on employees, working on commission basis;
select * from employees where commission_pct is not null
5) Find all employees with managers with id 108 or unknown, making
more than $7,700 a month;
select * from employees where (manager_id=108 or MANAGER_ID is not null)
and salary>7700
6) Display all unique numeric department ids, sorted by highest department number on top;
select distinct department_id from employees order by department_id desc
7) Please select all information from table “Employees” where last name contains “or”;
select * from employees where last_name like '%or%'
8) Our company is running a special promotion for all employees with last name starting with ‘D’. To qualify, you need to work in the department 50, 60, 90, or 100, make over $3000, and start working for our company from Jan 1, 1989 through Jan 1, 2002.
select * from employees where last_name like 'D%' and department_id in (50,60,90,100) and salary>3000 and hire_date between '01/01/1989' and '01/01/2002'
9) Company is planning to celebrate the 1 employee. When s/he was hired?
select MIN(TO_CHAR(hire_data, 'YYYY/MM/DD')
from employees
10) Get list of departments and number of employees in each department, ordered alphabetically.
select department_id, COUNT(*)
from employees
GROUP BY department_id
order by department_id
Lesson 4
select department_id, min(salary), max(salary)
from employees
where department_id in (80,90,100)
group by department_id
order by department_id
select department_id, COUNT(*)
from employees
GROUP BY department_id
order by department_id
select department_id, SUM(salary)
from employees
GROUP BY department_id
order by sum(salary) desc
select department_id, min(salary), max(salary)
from employees
where department_id in (80,90,100)
group by department_id
order by department_id
SELECT department_id, salary, COUNT(*)
FROM employees
GROUP BY department_id, salary
ORDER BY department_id desc
SELECT first_name, COUNT(*)
FROM employees
group by first_name
having count(*)>1
order by count(*) desc
select max(count(*)) from employees
group by first_name
select first_name, COUNT(*) from employees
group by first_name
having count(*)=(select max(count(*)) from employees
group by first_name)
order by first_name
_____
most popular last names
select last_name, COUNT(*) from employees
group by last_name
having count(*)=(select max(count(*)) from employees
group by last_name)
order by last_name
____
select first_name, last_name, salary, department_id from employees
where department_id =(select department_id from departments where department_name ='Shipping')
_____
hw
select salary, count(*)
from employees
group by salary
______
select department_id, sum(salary)
from employees
group by department_id
order by sum(salary)
select department_id, count(employee_id)
from employees
having count(employee_id)>10
group by department_id
SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
ORDER BY first_name
HAVING COUNT(*)>1
SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
HAVING COUNT(*)>1
ORDER BY first_name
select max (count(*)) from employees to find max people who have the sa
group by first_name
SELECT first_name, COUNT(*) most popular first name
FROM employees
having count(*)=(select max (count(*)) from employees
group by first_name)
order by first_name
SELECT last_name, COUNT(*)
FROM employees
GROUP BY last_name
HAVING COUNT(*)= (SELECT MAX(COUNT(*)) FROM employees
GROUP BY last_name)
ORDER BY last_name
SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM employees GROUP BY first_name)
ORDER BY first_name
______
SELECT department_id FROM departments
where department_name='IT'
SELECT first_name, last_name, salary, department_id from employees
WHERE department_id = 60
SELECT first_name, last_name, salary, department_id from employees
WHERE department_id = (SELECT department_id FROM departments
where department_name='IT')
_____
select employees.first_name, employees.last_name, departments.department_name
from employees, departments
where employees.department_id=departments.department_id
select e.first_name, e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id
Page updated
Google Sites
Report abuse