Презентация Data Modeling and Databases Lab 3: Introduction to SQL онлайн
На нашем сайте вы можете скачать и просмотреть онлайн доклад-презентацию на тему Data Modeling and Databases Lab 3: Introduction to SQL абсолютно бесплатно. Урок-презентация на эту тему содержит всего 23 слайда. Все материалы созданы в программе PowerPoint и имеют формат ppt или же pptx. Материалы и темы для презентаций взяты из открытых источников и загружены их авторами, за качество и достоверность информации в них администрация сайта не отвечает, все права принадлежат их создателям. Если вы нашли то, что искали, отблагодарите авторов - поделитесь ссылкой в социальных сетях, а наш сайт добавьте в закладки.
Оцените презентацию от 1 до 5 баллов!
- Тип файла:ppt / pptx (powerpoint)
- Всего слайдов:23 слайда
- Для класса:1,2,3,4,5,6,7,8,9,10,11
- Размер файла:507.31 kB
- Просмотров:68
- Скачиваний:0
- Автор:неизвестен
Слайды и текст к этой презентации:
№1 слайд
Содержание слайда: Data Modeling and Databases
Lab 3: Introduction to SQL
Bulat Gabbasov, Albina Sayfeeva
Innopolis University
2016
№2 слайд
Содержание слайда: Basic SQL query structure
Basic SQL query structure consists of SELECT, FROM, WHERE, GROUP BY and ORDER BY clauses.
SELECT [ALL | DISTINCT] expressions
specifies the columns to appear in the result
distinct keyword can be used to eliminate duplicates
FROM from_items
specifies the relations to be used
WHERE condition
filters the tuples
GROUP BY expression
groups rows with the same column values
the HAVING construct can be used to further filter the groups
ORDER BY expression
defines the order of the resulting tuples
№3 слайд
Содержание слайда: Data manipulation
INSERT
Inserts a tuple into the specified table
INSERT INTO tablename (list of columns)
VALUES (list of values), ...
UPDATE
Updates all tuples that match specified condition
UPDATE tablename SET column = newvalue, ...
WHERE condition
DELETE
Deletes all tuples that match specified condition
DELETE FROM tablename WHERE condition
№4 слайд
Содержание слайда: Inserting
Create a new student Harvey Specter:
INSERT INTO students
(student_id, firstname, lastname) VALUES
(1, 'Harvey', 'Specter')
№5 слайд
Содержание слайда: Updating
Change firstname of all students having student_id = 1 to ‘John’:
UPDATE students
SET firstname = 'John'
WHERE student_id = 1
№6 слайд
Содержание слайда: Deleting
Delete student having student_id = 1 from table students:
DELETE FROM students WHERE student_id = 1
№7 слайд
Содержание слайда: Expressions
Calculate expression 1 + 1 and name it as two:
SELECT 1 + 1 AS two
№8 слайд
Содержание слайда: Tables
Return list of all students:
SELECT * FROM students
№9 слайд
Содержание слайда: Exercise
Insert a new department named ‘Machine Learning’ and leaded with professor identified by professor_id = 1
INSERT INTO departments
VALUES (4, 'Machine Learning', 1)
Change name of the newly created department to ’Advanced Machine Learning’
UPDATE departments
SET name = 'Advanced Machine Learning'
WHERE name = 'Machine Learning’
Delete new newly created department
DELETE FROM departments
WHERE name = 'Advanced Machine Learning'
№10 слайд
Содержание слайда: Exercise
Find the address of the student with first name "Donna”
SELECT address FROM students
WHERE firstname = 'Donna'
№11 слайд
Содержание слайда: Exercise
Find all students who are either male or are from Kazan
SELECT * FROM students
WHERE gender = 'm' or address = 'Kazan'
№12 слайд
Содержание слайда: Exercise
Find all courses that worth at least 9 credits and are given by MSIT department
Hint: department_id for MSIT-SE is 1.
SELECT * FROM courses
WHERE credits >= 9 AND department_id = 1
№13 слайд
Содержание слайда: Exercise
Find names and salaries of professors who earn less than 15 000
SELECT firstname, lastname, salary
FROM professors WHERE salary < 15000
№14 слайд
Содержание слайда: Exercise
Find students born earlier than 1980
SELECT * FROM students
WHERE birthdate < '1980-01-01'
№15 слайд
Содержание слайда: Exercise
List full names of all students living in Moscow
Hint: concatenation operator a || b
SELECT
firstname || ‘ ‘ || lastname AS fullname
, address
FROM Students WHERE address = ‘Moscow’
№16 слайд
Содержание слайда: Exercise
Find students who's address contains "k" letter
SELECT * FROM students WHERE address LIKE '%k%'
№17 слайд
Содержание слайда: Exercise
Find students who's lastname consists of 7 letters and ends with "n”
SELECT * FROM students
WHERE lastname LIKE '______n'
№18 слайд
Содержание слайда: Exercise
Order and display students by lastname (alphabetically)
SELECT * FROM students
ORDER BY lastname
№19 слайд
Содержание слайда: Exercise
Order and display students by lastname and then by firstname (alphabetically)
SELECT * FROM students
ORDER BY lastname, firstname
№20 слайд
Содержание слайда: Exercise
Order by login : first letter of firstname + full lastname in descending order
Hint: use SUBSTRING(column from begin for length)
SELECT SUBSTRING(firstname from 1 for 1)
|| lastname AS login, *
FROM students
ORDER BY 1 DESC
№21 слайд
Содержание слайда: Exercise
Find names of male students who got more than 50 for any course
№22 слайд
Содержание слайда: Exercise
Which students are enrolled in DMD course?
№23 слайд
Содержание слайда: QA?
Скачать все slide презентации Data Modeling and Databases Lab 3: Introduction to SQL одним архивом:
