Data Modeling and Databases Lab 3: Introduction to SQL презентация

Содержание


Презентации» Информатика» Data Modeling and Databases Lab 3: Introduction to SQL
Data Modeling and Databases Lab 3: Introduction to SQL
 Bulat Gabbasov,Basic SQL query structure
 Basic SQL query structure consists of SELECT,Data manipulation
 INSERT
 Inserts a tuple into the specified table
 INSERTInserting 
 Create a new student Harvey Specter:
 INSERT INTO studentsUpdating
 Change firstname of all students having student_id = 1 toDeleting
 Delete student having student_id = 1 from table students:
 DELETEExpressions
 Calculate expression 1 + 1 and name it as two:
Tables
 Return list of all students:
 SELECT * FROM studentsExercise
 Insert a new department named ‘Machine Learning’ and leaded withExercise
 Find the address of the student with first name "Donna”
Exercise
 Find all students who are either male or are fromExercise
 Find all courses that worth at least 9 credits andExercise
 Find names and salaries of professors who earn less thanExercise
 Find students born earlier than 1980
 SELECT * FROM studentsExercise
 List full names of all students living in Moscow
 Hint:Exercise
 Find students who's address contains "k" letter 
 SELECT *Exercise
 Find students who's lastname consists of 7 letters and endsExercise
 Order and display students by lastname (alphabetically)
 SELECT * FROMExercise
 Order and display students by lastname and then by firstnameExercise
 Order by login : first letter of firstname + fullExercise
 Find names of male students who got more than 50Exercise
 Which students are enrolled in DMD course?QA?



Слайды и текст этой презентации
Слайд 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?


Скачать презентацию на тему Data Modeling and Databases Lab 3: Introduction to SQL можно ниже:

Похожие презентации