Podcast
Questions and Answers
Which command is used to retrieve data from a database?
Which command is used to retrieve data from a database?
- SELECT (correct)
- INSERT
- UPDATE
- DELETE
What does the INSERT statement do in SQL?
What does the INSERT statement do in SQL?
- Retrieves data from a table
- Adds new rows of data to a table (correct)
- Removes data from a table
- Modifies existing data in a table
Which of the following is a valid syntax for inserting multiple rows into a table?
Which of the following is a valid syntax for inserting multiple rows into a table?
- INSERT INTO table_name (column_list) INSERT VALUES (value_list_1), (value_list_2);
- INSERT INTO table_name (col1, col2) VALUES (value1, value2), (value3, value4); (correct)
- INSERT INTO table_name (col1, col2) PUT VALUES (value1, value2), (value3, value4);
- INSERT INTO table_name (col1, col2) ADD VALUES (value1, value2), (value3, value4);
Which command is NOT part of the Data Manipulation Language (DML)?
Which command is NOT part of the Data Manipulation Language (DML)?
Which action would use the DELETE command in SQL?
Which action would use the DELETE command in SQL?
What is the purpose of the UPDATE command in SQL?
What is the purpose of the UPDATE command in SQL?
Which of the following is the correct syntax to update one column in a row?
Which of the following is the correct syntax to update one column in a row?
What must be included when updating multiple columns in a row?
What must be included when updating multiple columns in a row?
Which statement best describes the optional WHERE clause in the UPDATE command?
Which statement best describes the optional WHERE clause in the UPDATE command?
In an example of updating several columns in a row, which format is correct?
In an example of updating several columns in a row, which format is correct?
Flashcards
DML in SQL
DML in SQL
Data Manipulation Language (DML) is a set of SQL commands used to change the data within a database table.
INSERT statement
INSERT statement
An SQL command used to add new rows (records) of data to a table.
INSERT syntax (single row)
INSERT syntax (single row)
INSERT INTO table_name (column1, column2, ...) VALUES ('value1', 'value2', ...);
INSERT syntax (multiple rows)
INSERT syntax (multiple rows)
Signup and view all the flashcards
SELECT statement
SELECT statement
Signup and view all the flashcards
UPDATE statement
UPDATE statement
Signup and view all the flashcards
SQL database
SQL database
Signup and view all the flashcards
UPDATE table_name SET column_1 = new_value
UPDATE table_name SET column_1 = new_value
Signup and view all the flashcards
WHERE clause (UPDATE)
WHERE clause (UPDATE)
Signup and view all the flashcards
DML (SQL)
DML (SQL)
Signup and view all the flashcards
Study Notes
Database Design Fundamentals
- Database Design is a process, not a single task
- DML (Data Manipulation Language): commands for managing data in a database
- DML used for inserting, updating, deleting, retrieving data
- DML used by developers and database administrators to manage data
- DML often used in combination with DDL
- DDL (Data Definition Language) defines and modifies the structure of database objects
DML Commands
- SELECT: Used to retrieve data from one or more tables
- INSERT: Used to add new rows of data to a table
- UPDATE: Used to modify existing rows of data in a table
- DELETE: Used to remove one or more rows of data from a table
INSERT Command Syntax
- Insert a row:
INSERT INTO table_name (col1, col2, ...) VALUES ("value1", "value2", ...);
Example:INSERT INTO Person (id, LastName, City) VALUES (222, 'Hassan', 'Assuit');
- Insert multiple rows:
INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), ... (value_list_n);
SQL Server Processing Order
- SQL Server processes the
FROM
clause first, then theSELECT
clause.
SQL Comparison and Other Conditions
- Comparison: = (equal), > (greater than), >= (greater than or equal), < (less than), <= (less than or equal), <> or != (not equal)
- Other: BETWEEN (inclusive range), IN (match any value from a list), LIKE (match a pattern), IS NULL (is a null value), IS NOT NULL (is not a null value)
Logical Conditions (Precedence)
- Logical: AND, OR, NOT
- Precedence Rules: Operations are performed in order, prioritizing Arithmetic Operators, followed by Concatenation, then Comparisons, then NOT, IN, BETWEEN, then logical conditions like AND and OR.
Predicates and Operators
-
Common Predicates: IN, BETWEEN, LIKE
-
Logical Operators: AND, OR, NOT
-
Comparison Operators: =, >, >=, <, <=, <>, !=
-
Arithmetic Operators: +, -, *, /, %
-
Concatenation: +
Example Queries (Student Data)
- Query to retrieve all platforms where students haven't graduated:
SELECT name FROM Platform WHERE graduate = 1;
- Query to retrieve students who have a grade of 'A' and attendance greater than or equal to 15:
SELECT appno, pfname, Foundgrade, attd FROM Students_platform WHERE Foundgrade = 'A' AND attd >= 15;
- Query to retrieve students from Science or Engineering Faculty:
SELECT S.appno, S.name, F.faculty FROM Students S JOIN Fac_majors F ON S.f_code = F.f_code WHERE F.faculty = 'Science' OR F.faculty = 'Engineering';
Database Table Assignment/Schema
- The assignment describes creating and populating a "CompanyDB" database with several tables including
Employee
,Department
,Works_for
,Project
,Dependent
. - Specific data is also included within data tables for sample values (e.g., employee names, address, salary etc).
Query examples/instructions
- Query to display project information starting with the letter 'a'
- Query to list employees in department 30 earning between 1000-2000 in salary
- Query to display employees working in department 10
- Query to display female employees under a supervisor whose id is 223344
- Query to show the first and last names along with supervisor's id for employees.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.