i. Short answers ############################ 1. - 5 pts - Explain what DML and
ID: 3840525 • Letter: I
Question
i. Short answers
############################
1. - 5 pts -
Explain what DML and DDL are, what they are used for, and what is the difference between the two.
2. - 5 pts -
What is the purpose of the xrefArtistsMembers table, why did they design the schema that way? Why don't we just relate artists and members together directly like Titles and Tracks for example?
3. - 7 pts -
You write the following query to find all members who do not belong to Sonata:
SELECT M.MemberID, M.lastname FROM Members M LEFT JOIN XrefArtistsMembers X ON(M.MemberID = X.MemberID) LEFT JOIN Artists A ON (X.ArtistID = A.ArtistID AND A.Artistname = "Sonata") WHERE A.ArtistID IS NULL;
Your friend argues, that the query must be wrong, because
a) the ON clause is looking for ArtistName = "Sonata" which is not what we want
b) the WHERE clause is looking for Artists where the ArtistID is NULL, which doesn't make sense, since all Artists have an ArtistID
Is your friend right? How do you explain each of these 2 issues?
Explanation / Answer
1)Answer:
DML:A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for selecting, inserting, deleting and updating data in a database. Performing read-only queries of data is sometimes also considered a component of DML.
DDL:
Data Definition Language (DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER, and DROP.
uses of dml and ddl:
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
differences:
->DDL statements are used for creating and defining the Database structure. DML statements are used for managing data within Database
-> DDL statements are CREATE, ALTER, DROP, TRUNCATE, RENAME etc. DML statements are SELECT, INSERT, DELETE, UPDATE, MERGE, CALL etc
->DDL statements work on whole table. CREATE will a create a new table. DROP will remove the whole table. TRUNCATE will delete all records in a table. DML statements can work on one or more rows. INSERT can insert one or more rows. DELETE can remove one or more rows.
->DDL statements do not have a WHERE clause to filter the data. Most of DML statements support filtering the data by WHERE clause.
->Changes done by a DDL statement can not be rolled back. So there is no need to issue a COMMIT or ROLLBACK command after DDL statement. We need to run COMMIT or ROLLBACK to confirm our changed after running a DML statemen
->Since each DDL statement is permanent, we can not run multiple DDL statements in a group like Transaction. DML statements can be run in a Transaction. Then we can COMMIT or ROLLBACK this group as a transaction. Eg. We can insert data in two tables and commit it together in a transaction.
-> After DDL statements no triggers are fired. But after DML statements relevant triggers can be fired.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.