CS 364 Midterm
Which SQL statement is used to delete data from a database?
DELETE
With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?
DELETE FROM Persons WHERE FirstName = 'Peter'
Almost all DBMS's allow their applications to access data in their databases without using any part of the DBMS's.Almost all DBMS's allow their applications to access data in their databases without using any part of the DBMS's. Select one: True False
False
Database related research activities were popular before the concepts in Operating Systems were introduced. Select one: True False
False, DBMS's and databases are after OS.
According to Exercise 1, MongoDB is the best relational DBMS nowadays on the market. Select one: True False
False, MongoDB is not a relational DBMS. According to definition of databases, MongoDB cannot even create databases.
The terms database system means the same as DBMS, or just database. Select one: True False
False. We use a DBMS to create, manage, and access data in a database. A database system must have a DBMS, at least one database. In addition a database system has users and an application.
With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
INSERT INTO Persons (LastName) VALUES ('Olsen')
With SQL, how can you insert a new record into the "Persons" table?
INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
How to represent aggregated data in SQL
If you want certain information segmented by some dimensions i.e SUM, AVG, COUNT, you have to use GROUP BY. If you want to filter out certain groups use HAVING. ex:/ SELECT ClassName FROM ENROLLMENT GROUP BY ClassName HAVING count(*) >= 10
Self-Referencing in SQL
Like Rename. For example: SELECT C.Room, C.Name, C2.Name FROM Class as C join Class as C2 on C.Room = C2.Room and C.Name < C2.Name
Components of a database
Metadata: So a database is self-describing. User data: For a database to be useful it needs to have something useful in it. Indexes: For performance reasons. It doesn't need them, but would be slow doing its queries.
Which SQL statement is used to extract data from a database?
SELECT
An SQL statement does not always return a relation. Why?
1) The result may have a duplicated rows 2) The order of rows matters if you use Order By 3) The order of the columns matters because if you say Select FirstName, LastName, then the first column is the firstname. Then the order of columns matter and any other column cannot be the first column except FirstName.
What is a database
A self-describing collection of integrated records that are computerized
Which of the following statements regarding databases is false? Select one: a. Applications need to use DBMS to access data in databases. b. Data in a database do not have to be persistent. For example, we could easily record the water level of a river, even it is constantly changing. c. Microsoft Access is not considered as an enterprise level database management system (DBMS). d. A DBMS can host several databases. An application may access several databases that hosted by different DBMS's.
B
With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?
SELECT * FROM Persons WHERE FirstName LIKE 'a%'
Syntax of a SQL Group By Statement vs Having
Select | Select From | From Where | Where Group By | Group By Order By | Having | Order By
What does SQL stand for?
Structured Query Language
The differences between Sub-queries and Joins.
Sub-queries cannot display columns that are not in the tables of the top most level.
Which of the following relational algebra expressions give SID for students not taking CS364, assuming the S-E-C set of relations are used. Select one: a. E where ClassName != 'CS364' [StudentNumber] b. S where ClassName != 'CS364' [StudentNumber] c. S[SID] - E where ClassName != 'CS364' [StudentNumber] d. S[SID] - E where ClassName = 'CS364' [StudentNumber]
The correct answer is: S[SID] - E where ClassName = 'CS364' [StudentNumber]
Which of the following statements is not true for an enterprise-class database system? Select one: a. The database application(s) interact(s) with the DBMS. b. The database application(s) directly access(es) the database data. c. The DBMS accesses the database data. d. The application generates SQL statements.
The correct answer is: The database application(s) directly access(es) the database data.
A database management system (DBMS) creates, processes and administers databases. Select one: True False
True
Microsoft Access is not a database per our definition of databases. Select one: True False
True
Which statement below is not true for an enterprise level database management system? Select one: a. It can support thousands concurrent users. b. It is OS independent. For example, MySQL can run on both Windows and Linux OS's. But the file for Windows cannot be used for Linux, and vice versa. c. Generally speaking, it should support SQL (Structured Query Language). d. It has many features such as backing up and restoring.
B, DBMS are very OS dependent.
Which one of the following is not a component of a database system? Select one: a. User b. DBMS c. Metadata d. A database
C. Metadata. Metadata is part of a database, not a database system.
Which of the following is not an relational algebra operation we discussed? Select one: a. UNION b. JOIN c. SELECTION d. WHERE
D. WHERE
Which SQL statement is used to insert new data in a database?
INSERT INTO
The basics of an SQL Statement
Must have SELECT, if you need to refer to a table use FROM, if you do not want to have every record or have a specified condition use WHERE, and if you need to sort use ORDER BY
The description of a database's structure that is stored within the database itself is called the metadata. Select one: True False
True
Per our definition of "Database," MongoDB is not a DBMS, at least not a relational DBMS Select one: True False
True (MongoDB is not a relational DBMS)
DBMSs are OS dependent. Select one: True False
True, Can you install SQL Server 2016 on Unix? No. Can you install the same MySQL file on both Windows and Linux, no they each requires their own file.
One of the main reasons of having databases is to share information. Select one: True False
True, other reasons are to reduce redundancy and inconsistent data, support transaction and data integrity, enforce security, support standards, and meet complicated requirements.
A well designed database is capable of reducing redundancy, therefore, minimizing data inconsistency. Select one: True False
True, this is why we have databases
Which SQL statement is used to update data in a database?
UPDATE
How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
The four components of a database
Users, database applications, DBMS, database
The conditions that makes a relation closed
When the operands are both relations and the output is a relation as well.
