Database unit 4-5-6
Which of the following statements is wrong? Select one: a. Aggregate functions are mostly used in the WHERE clause of a SELECT statement. b. When using the equivalence operator (=) in the predicate, you must make sure that the subquery retrieves exactly one value. c. SQL's concept of joining two or more tables also applies to joining two copies of the same table. d. Output of the COUNT() function is the number of rows or column values that would be selected by the query.
a. Aggregate functions are mostly used in the WHERE clause of a SELECT statement.
The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T WHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID MC a. Equi-join b. Natural join c. Outer Join d. Cartesian Join
a. Equi-join
Which of the following statements is true concerning subqueries? Select one: a. Involves the use of an inner and outer query b. Cannot return the same result as a query that is not a subquery. c. Does not start with the word SELECT. d. All of the above.
a. Involves the use of an inner and outer query
CREATE TABLE BOOK ( BookID CHAR(20) NOT NULL, Title VARCHAR(30) NOT NULL, PublisherName VARCHAR(20), PRIMARY KEY (BookID) ); Considering SQL statement above, which of the following is wrong? Select one: a. PublisherName can't be null b. The name of the created table is BOOK c. There are three columns in this table d. Primary key of this table is BookID
a. . PublisherName can't be null
Which of the following is not a Numeric Type? Select one: a. BOOLEAN ü b. TINYINT c. SMALLINT d. BIGINT
a. BOOLEAN
Which of the following includes a set of commands used to control access to the data within the database, including security? Select one: a. DCL b. DML c. DDL d. DAL
a. DCL
Which of the following includes a set of commands used to control access to the data within the database, including security? Select one: a. DCL b. DML ý c. DDL d. DAL
a. DCL
CREATE TABLE BOOK ( BookID CHAR(20) NOT NULL, Title VARCHAR(30) NOT NULL, PublisherName VARCHAR(20), PRIMARY KEY (BookID) ); Considering SQL statement above, which of the following is wrong? a. PublisherName can't be null ü b. The name of the created table is BOOK c. There are three columns in this table d. Primary key of this table is BookID
a. PublisherName can't be null
Question text An atomic field is Select one: a. a single valued field b. a key field c. a numerical field d. a key that contains only a single field
a. a single valued field
Which of the following data types used in SQL could be used to define a fixed-length text field of 20 characters? Select one: a. char(20) ü b. varchar(20) c. fixed(20) d. bit(20)
a. char(20)
Which of the following SQL command is used for creating a database? Select one: a. create schema ü b. create table c. create view d. create file
a. create schema
The rule that specifies that there should be no repeating fields and that fields should be atomic is Select one: a. first normal form b. second normal form c. third normal form d. None of the above
a. first normal form
Which SQL statement is used to insert new data in a database? Select one: a. insert into ü b. insert new c. add new d. add record
a. insert into ü
With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"? Select one: a. SELECT * FROM Persons SORT BY 'FirstName' DESC b. SELECT * FROM Persons ORDER BY FirstName DESC c. SELECT * FROM Persons ORDER FirstName DESC d. SELECT * FROM Persons SORT 'FirstName' DESC
b. SELECT * FROM Persons ORDER BY FirstName DESC
Create Table EXAMS ( SUB_NO integer Not Null, STUDENT_NO integer Not Null, MARK decimal (3), DATE_TAKEN date ); The above SQL statement is used to create the EXAMS table. Considering the EXAMS table; which of the following SQL statements is used to define the primary key of the EXAMS table? Select one: a. Alter Table EXAMS Primary Key (SUB_NO); ý b. Alter Table EXAMS Add Primary Key (SUB_NO); c. Alter Table Add SUB_NO Primary Key (EXAMS); d. Alter Table SUB_NO Add Primary Key (EXAMS);
b. Alter Table EXAMS Add Primary Key (SUB_NO);
Create Table EXAMS ( SUB_NO integer Not Null, STUDENT_NO integer Not Null, MARK decimal (3), DATE_TAKEN date); The above SQL statement is used to create the EXAMS table. Considering the EXAMS table; which of the following SQL statements is used to define the primary key of the EXAMS table? a. Alter Table EXAMS Primary Key (SUB_NO); b. Alter Table EXAMS Add Primary Key (SUB_NO); c. Alter Table Add SUB_NO Primary Key (EXAMS); d. Alter Table SUB_NO Add Primary Key (EXAMS);
b.Alter Table EXAMS Add Primary Key (SUB_NO);
Consider the following database: MOVIE(id,title,yr)ACTOR(id,name)CASTING(movieid,actorid) Identify the SQL command which will return the titles of all 1959 Marilyn Monroe films. Option 1. The following SQL... SELECT title FROM movie,casting,actor WHERE movieid = movie.id<> AND name = 'Marilyn Monroe' ; Option 2. The following SQL... SELECT title FROM movie,actor WHERE name = 'Marilyn Monroe' AND yr = 1959 ; Option 3. The following SQL... SELECT title FROM movie,casting,actor WHERE movieid = movie.id AND actor.id = actorid AND name = 'Marilyn Monroe' AND yr = 1959 ; Option 4. The following SQL... SELECT title FROM movie,casting,actor WHERE movieid = movie.id AND actor.id = actorid AND movie.yr = casting.yr AND name = 'Marilyn Monroe' AND yr = 1959 ;
Option 3
Which of the following SQL statements that are used to create tables gives an error? a. Create Table Students( Name char(10) Not Null, SureName char (40)); b. Create Table StudentsFriends( Name char(20) Not Null, SureName varchar (50)); c. Create Table StudentsFriendsandFamily( Name char(30) Not Null, Name varchar (40)); d. Create Table StudentsFriendsandInstructors( Name char(30) Not Null, SureName varchar (30));
c. Create Table StudentsFriendsandFamily( Name char(30) Not Null, Name varchar (40));
Which of the following is a UNION query? a. Combines the output from no more than two queries and must include the same number of columns. b. Combines the output from no more than two queries and does not include the same number of columns. c. Combines the output from multiple queries and must include the same number of columns. d. Combines the output from multiple queries and does not include the same number of columns.
c. Combines the output from multiple queries and must include the same number of columns.
Which of the following is a UNION query? Select one: a. Combines the output from no more than two queries and must include the same number of columns. b. Combines the output from no more than two queries and does not include the same number of columns. c. Combines the output from multiple queries and must include the same number of columns. d. Combines the output from multiple queries and does not include the same number of columns.
c. Combines the output from multiple queries and must include the same number of columns.
A given relation is known to be in third normal form. Select the statement which can be inferred from this: Select one: a. All attributes contribute to the primary key b. Each non-key attribute determines the primary key c. Each non-key attribute is determined by the primary key d. Every determinant is a candidate key e. The relation is not in fourth normal form.
c. Each non-key attribute is determined by the primary key
A lack of normalisation can lead to which one of the following problems Select one: a. Lost Updates b. Deletion of data c. Insertion problems d. Deadlock conditions
c. Insertion problems
Which of the following SQL command is used for adding one or more new columns to a table? Select one: a. create schema b. create table c. alter table ü d. alter file
c. alter table
Which of the following SQL command is used to define a logical table from one or more tables or views? Select one: a. create table b. define table c. create view ü d. create database
c. create view
The rule that prohibits transitive dependencies is Select one: a. first normal form b. second normal form c. third normal form d. BCNF
c. third normal form
Data integrity is concerned with ensuring that any new data that is added to the tables is compatible with the existing inter-table relationships.
True
Data integrity is concerned with ensuring that any new data that is added to the tables is compatible with the existing inter-table relationships. Select one: True False
false
Primary keys must have different values for each table row. Select one: True False
true
SELECT LECTURERS.SURNAME, LECTURERS.SURNAMEFROM LECTURERS, LECTURERSWHERE DEPT_NO = DEPT_NO ; The above query is syntactically correct.
true
SELECT LECTURERS.SURNAME, LECTURERS.SURNAMEFROM LECTURERS, LECTURERSWHERE DEPT_NO = DEPT_NO ; The above query is syntactically correct. Select one: True False
true
When an accounting application creates new orders in the orders table, which SQL statements of the following should be used? Select one: a. DCL b. DML c. DDL d. DAL
b. DML
Given the table Instructor (InstructorID, Name, Course), which of the following SQL statements would be used to add new instructor data to the Instructor table? Select one: a. INSERT INTO Instructor SET InstructorID =1111, Name=David, Course =CS 2204 b. INSERT INTO Instructor VALUES (1111, 8David9, ' CS 2204') ü c. INSERT INTO New Instructor VALUES (1111, 8David9, ' CS 2204') d. INSERT INTO Instructor VALUES (8David9, 1111, ' CS 2204')
b. INSERT INTO Instructor VALUES (1111, 8David9, ' CS 2204') ü
Which of the following is generally a benefit of normalisation? Select one: a. Performance is improved b. Insertion anomalies are avoided c. Selection anomalies are avoided d. Number of tables is reduced
b. Insertion anomalies are avoided
Question text It is impossible to represent which of the following in a relational schema? Select one: a. any mandatory participation constraint in a many-to-one relationship b. any mandatory participation constraint in a many-to-many relationship c. a one-to-one relationship d. a many-to-one relationship e. a ternary relationship
b. any mandatory participation constraint in a many-to-many relationship
Assume that you are dealing with money and you do a lot of calculations that you want them to be exact. Which of the following data types is the best option to store this in a relation? Select one: a. integer b. decimal(10,2) ü c. varchar(12) d. char(12)
b. decimal(10,2)
Which SQL keyword is used to sort the result-set? Select one: a. SORT BY b. ORDER c. ORDER BY d. SORT
c. ORDER BY
Which of the following statements is wrong? a. The CREATE TABLE command is a part of SQL's DDL b. The ALTER TABLE command is a part of SQL's DDL c. The DROP TABLE command is a part of SQL's DDL d. The SELECT TABLE command is a part of SQL's DDL
d. The SELECT TABLE command is a part of SQL's DDL
To transform a relation from second normal form to third normal form we must remove which one of the following? Select one: a. All partial-key dependencies b. All inverse partial-key dependencies c. All repeating groups d. All transitive dependencies
d. All transitive dependencies
Question text One of the tasks of a relational database management system (RDBMS) is to ensure that relational schemas are in at least 3NF. Select one: True False
false
Structured Query Language (SQL) is not a complete programming language. Select one: True ü False
true
The standards for SQL has been maintained by The American National Standards Institute (ANSI). Select one: True False
true
An athletics meeting involves several competitors who participate in a number of events. The database is intended to record who is to take part in which event and to record the outcome of each event. As results become available the winner attribute will be updated with the cid of the appropriate competitor. Competitor(cid, name, nationality) Event(eid, description, winner) Competes(cid, eid) Identify the result of the following SQL statement: SELECT eid FROM Competes, Competitor WHERE Competes.cid=Competitor.cid AND nationality = 'Swedish'
2
Which of the following SQL statements that are used to create tables gives an error? a. Create Table Instructor( ID char(10) Not Null, Name varchar (40), SureName varchar (40)); b. Create Table Instructor( ID integer Not Null, Name varchar (40), SureName varchar (40),CONSTRAINT pk Primary Key (ID)); c. Create Table Instructor( ID integer Not Null, Name varchar (40), SureName varchar (40),CONSTRAINT pk Unique (ID)); d. Create Table Instructor( ID integer Not Null, Name varchar (40), SureName varchar (40),CONSTRAINT pk Primary Key (ID),CONSTRAINT pk Unique (ID));
d. Create Table Instructor( ID integer Not Null, Name varchar (40), SureName varchar (40),CONSTRAINT pk Primary Key (ID),CONSTRAINT pk Unique (ID));
Which SQL statement is used to return only different values? Select one: a. SELECT UNIQUE b. SELECT IDENTITY c. SELECT DIFFERENT d. SELECT DISTINCT
d. SELECT DISTINCT
A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user? Select one: a. Select e.empid, d.head from emp e, dept d; b. Select e.empid, d.head from emp e, dept d where e.dept# = d.dept#; c. Select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#; d. Select e.empid, d.head from emp e right outer join dept d on e.dept# = d.dept#;
d. Select e.empid, d.head from emp e right outer join dept d on e.dept# = d.dept#;
Which of the following statements is wrong? Select one: a. The CREATE TABLE command is a part of SQL's DDL b. The ALTER TABLE command is a part of SQL's DDL ý c. The DROP TABLE command is a part of SQL's DDL d. The SELECT TABLE command is a part of SQL's DDL
d. The SELECT TABLE command is a part of SQL's DDL
What are the basic categories of the SQL language based on functionality? Select one: a. Data definition b. Data modification ý c. Data control d. All the above
d. all the above
We know that table Q has only one candidate key. Q is in 2NF. Select one: True False
false
The CREATE TABLE command creates an empty table-one with no records.
true
The CREATE TABLE command creates an empty table-one with no records. Select one: True ü False
true
The UPDATE command in SQL is used to change the existing values of the columns.
true
The UPDATE command in SQL is used to change the existing values of the columns. Select one: True False
true
The standards for SQL has been maintained by The American National Standards Institute (ANSI). Select one: True ü False
true
When using SQL create table command, defining constraints is optional. Select one: True ü False
true
