8.3 Databases.ddl&dml Flashcards
What is Data Definition Language (DDL) used for in a DBMS?
DDL is used to create, modify, and remove the data structures that form a relational database.
What is Data Manipulation Language (DML) used for in a DBMS?
DML is used to add, modify, delete, and retrieve the data stored in a relational database.
What is an SQL script?
An SQL script is a list of SQL commands that perform a given task, often stored in a file for reuse.
How do DDL and DML differ in function?
DDL works on the structure of the relational database, while DML works with the data stored in the database.
What language is commonly used for both DDL and DML in most DBMSs?
Structured Query Language (SQL), which has been the industry standard since the 1970s.
What is the purpose of the SQL command CREATE DATABASE?
It creates a new database
What does the SQL command CREATE TABLE do?
It creates a new table definition within a database
CREATE TABLE Student(
StudentID CHARACTER,
FirstName CHARACTER,
SecondName CHARACTER,
DateOfBirth DATE,
ClassID CHARACTER);
What is the function of the SQL command ALTER TABLE?
It changes the definition of an existing table
ALTER TABLE Student ADD PRIMARY KEY (StudentID)
How do you add a primary key to a table in SQL?
By using the PRIMARY KEY constraint within the CREATE TABLE or ALTER TABLE statement.
ALTER TABLE Student ADD PRIMARY KEY (StudentID)
How do you define a foreign key in SQL?
Using FOREIGN KEY (…) REFERENCES … to establish a relationship with a primary key in another table.
ALTER TABLE Student ADD FOREIGN KEY ClassID REFERENCES Class(ClassID)
What does the SQL data type CHARACTER represent?
A fixed-length text field
What does VARCHAR(n) represent in SQL?
A variable-length text field with a maximum of n characters
How is the BOOLEAN data type represented in SQL?
As TRUE or FALSE, but typically stored using the integers 1 (TRUE) and 0 (FALSE).
What kind of values does the INTEGER data type store in SQL?
integers dumbass
What is the REAL data type used for in SQL?
Numbers with decimal places
What is the typical format of the DATE data type in SQL?
YYYY-MM-DD
What is the typical format of the TIME data type in SQL?
HH:MM:SS
What is the function of the SQL command SELECT FROM?
It fetches data from a database and all queries begin with SELECT
How is the WHERE clause used in an SQL query?
It includes only rows in a query that match a given condition.
What does the ORDER BY clause do in SQL?
It sorts the results of a query by a given column, either alphabetically or numerically.
What is the purpose of the GROUP BY clause in SQL?
It arranges data into groups based on one or more columns
How does INNER JOIN work in SQL?
It combines rows from different tables where the join condition is true.
SELECT Teacher.TeacherName AND Subject.SubjectName FROM Teacher INNER JOIN Subject ON Teacher.LicenceNumber = Subject.LicenceNumber
What does the SQL function SUM do?
It returns the sum of all the values in a specified column.
SELECT SUM (ExamMark) FROM STUDENTSUBJECT
What does COUNT do in an SQL query?
It counts the number of rows where the column is not NULL
