SQL Exam 1 Study Set
Consider the following schema − LOCATIONS(subject_code, department_name, location_id, city); Which code snippet will alter the table LOCATIONS and add a column named Address, with datatype VARCHAR(100)? A. ALTER TABLE locations ADD address varchar(100); B. ALTER TABLE locations ADD COLUMN address varchar(100); C. MODIFY TABLE locations ADD address varchar(100); D. CHANGE TABLE locations ADD address varchar(100);
A. ALTER TABLE locations ADD address varchar(100);
Consider the following schema − LOCATIONS(subject_code, department_name, location_id, city); Which code snippet will alter the table LOCATIONS and change the datatype of the column city to varchar(30) AND change Subject_code to varch(10) A. ALTER TABLE locations ALTER COLUMN city varchar(30), subject_code varchar(10) B. ALTER TABLE locations ALTER COLUMN city varchar(30), Alter Column subject_code varchar(10) C. ALTER TABLE locations Modify COLUMN city varchar(30), Modify Column subject_code varchar(10) D. All are correct
A. ALTER TABLE locations ALTER COLUMN city varchar(30), subject_code varchar(10)
Which of the following code will successfully delete the table LOCATIONS from the database? A. DROP TABLE locations B. DELETE TABLE locations C. TRUNCATE TABLE locations D. b & c
A. DROP TABLE locations
Which of the following is not true about the ALTER TABLE statement? A. It can add a new row. B. It can add a new column. C. It can modify existing columns. D. It can define a default value for the new column.
A. It can add a new row.
Which of the following is true about the RDBMS? A. RDBMS stands for the Relational Database Management System. B. RDBMS is mandatory to use to create a database. C. Both a & b D. None of the above
A. RDBMS stands for the Relational Database Management System.
What does a primary key define? A. The columns that uniquely identify a row in a table B. The data that uniquely identifies a single column C. The quality of data in a table D. The relationship of data between two or more tables
A. The columns that uniquely identify a row in a table
Victoria is defining a table. Her company has 20,000 employees. What datatype must she use to define the EmployeeID column to use the least amount of disk storage, but allow for the identity to be set for the column? A. TinyINT B. SmallINT C. INT D. BIGINT
A. TinyINT (0-255) B. SmallINT (~-32,000-~32,000) Correct C. INT (-2 billion to 2 billion) D. BIGINT (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
Which of the following query would display all the students whose first name starts and ends with the character 'A'? A. select FirstName from students where FirstName like 'A%A' B. select FirstName from students where FirstName like 'A%A%' C. select FirstName from students where FirstName like '%A%A%' D. select FirstName from students where FirstName like '%A%A'
A. select FirstName from students where FirstName like 'A%A'
Which code snippet will alter the table LOCATIONS and delete the column named CITY? A. MODIFY TABLE locations DROP city varchar(30); B. ALTER TABLE locations DROP COLUMN city; C. ALTER TABLE locations DROP city; D. None of the above
B. ALTER TABLE locations DROP COLUMN city;
Which is a valid CREATE TABLE statement? A. Create table emp add (id integer) B. Create table emp (id integer) C. Create table emp modified (id integer) D. Create emp table (id integer)
B. Create table emp (id integer)
Which is not true about Identity columns A. Identity column is a numeric column in a table that is automatically populated with an integer value each time a row is inserted B. Identity columns are always defined as integer. C. Identity column's definitions must not allow null values D. The values automatically generated for each row inserted are based on the seed and an increment property of the identity column
B. Identity columns are always defined as integer.
Which is not true about Data Modeling definition and features? A. Data modeling is the process of creating a simplified diagram of a software system and the data elements it contains. B. It can NOT be created through reverse-engineering efforts since it always starts from scratch. C. Flowchart that illustrates data entities, their attributes, and the relationships between entities. D. It enables data management and analytics teams to document data requirements for applications and identify errors in development plans before any code is written.
B. It can NOT be created through reverse-engineering efforts since it always starts from scratch.
Databases can be classified by location. Which is not part of location classified database A. Centralized database B. Multiuser Database C. Distributed database D. Cloud database
B. Multiuser Database
What is a relation in RDBMS? A. Key B. Table C. Row D. Data Types
B. Table
You wish to create a table containing columns A, B, C, and D. You define columns B and D to allow NULL values. You define columns A and B as being the primary key. What will happen when you issue the Transact-SQL statement to create the table? A. The table will not be created unless all columns make up the primary key. B. The table will not be created and will result in error. C. The table will be created as directed, but without the primary key. D. The table will be created as directed.
B. The table will not be created and will result in error.
Which of the following query would display all the students whose first name starts and ends with the character 'A'? A. select FirstName from students where FirstName like 'A%' OR FirstName like '%A' B. select FirstName from students where FirstName like 'A%' AND FirstName like '%A' C. select FirstName from students where FirstName like 'A%A%' D. select FirstName from students where FirstName like '%A%A'
B. select FirstName from students where FirstName like 'A%' AND FirstName like '%A'
Which of the following is not true about constraints? A. A NOT NULL constraint specifies that the column cannot have a null value. B. A UNIQUE constraint specifies that a column or a combination of column must have unique values for all rows. C. A PRIMARY KEY is same as UNIQUE. D. A FOREIGN KEY enforces a foreign key relationship between a column and a referenced table.
C. A PRIMARY KEY is same as UNIQUE.
Consider the following schema − LOCATIONS(subject_code, department_name, location_id, city); Which code snippet will alter the table LOCATIONS and add a column named Address, with datatype VARCHAR(100)? A. ALTER TABLE locations ADD address varchar(100) NOT NULL; B. ALTER TABLE locations ADD COLUMN address varchar(100) NULL; C. ALTER TABLE locations ADD address varchar(100) NULL; D. CHANGE TABLE locations ADD address varchar(100) NOT NULL;
C. ALTER TABLE locations ADD address varchar(100) NULL;
Consider the following schema − LOCATIONS(subject_code, department_name, location_id, city); Which code snippet will alter the table LOCATIONS and change the datatype of the column city to varchar(30)? A. ALTER TABLE locations MODIFY COLUMN city varchar(30); B. MODIFY TABLE locations ADD city varchar(30); C. ALTER TABLE locations ALTER COLUMN city varchar(30); D. ALTER TABLE locations ALTER city varchar(30);
C. ALTER TABLE locations ALTER COLUMN city varchar(30);
Consider the following schema − LOCATIONS(subject_code, department_name, location_id, city); Which code snippet will alter the table LOCATIONS and change the datatype of the column cityto varchar(30)? A. ALTER TABLE locations MODIFY COLUMN city varchar(30); B. MODIFY TABLE locations ADD city varchar(30); C. ALTER TABLE locations ALTER COLUMN city varchar(30); D. ALTER TABLE locations ALTER city varchar(30);
C. ALTER TABLE locations ALTER COLUMN city varchar(30);
Which of the following clause allows us to extract data in a range? A. Like B. IN C. BETWEEN D. RANGE
C. BETWEEN
Which is NOT part of Data Wrangling? A. Identifying and resolving mistakes and filling in missing data. B. Cleaning raw data in preparation for analysis. C. Create hypotheses and testing them to answer question. D. Transferring data into an easily understandable format.
C. Create hypotheses and testing them to answer question.
What is the difference between Primary key and Unique key? A. Primary Key does not allow NULL values, while Unique Key allows multiple NULL Values B. Primary Key allows one NULL value while Unique Key does not allow NULL Value C. Primary Key does not allow NULL Values, while Unique key allows one NULL Value D. Primary Key allows one NULL values while Unique key allows multiple NULL Values
C. Primary Key does not allow NULL Values, while Unique key allows one NULL Value
Which of the following is a good database management practice? A. Adding redundant attributes B. Not specifying primary keys C. Removing redundant attribute D. None of the above
C. Removing redundant attribute
Which of the following is a good database management practice? A. Adding redundant attributes to make sure that the data is valid. B. Not specifying primary keys to a table since they will take storage space. C. Removing redundant attribute. D. None of the above
C. Removing redundant attribute.
Which query will list (select) the DepartmentName,Department ID and the average salary of the department as "AverageSalaryofDepartment" per department from the Employees and Department Tables. Only Select deprtments with Average Salary of $50000? order the result by department ID in ascending order and average Salary in a descending order. The output columns should be as follows: DepartmentName DepartmentId AverageSalaryofDepartment A. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId Having Avg (Salary)>50000.00 ORDER BY DepartmentId ASC,AVG(Salary)DESC B. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId WHERE Avg (Salary)>50000.00 GROUP BY DepartmentName, DepartmentId ORDER BY DepartmentId ,AVG(Salary) DESC C. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId GROUP BY DepartmentName, DepartmentId Having Avg(Salary) >50000 ORDER BY DepartmentId ,AverageSalaryofDepartment DESC D. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId WHERE Avg(Salary) > 50000 GROUP BY DepartmentName, DepartmentId ORDER BY DepartmentId ,AverageSalaryofDepartment DESC
C. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId GROUP BY DepartmentName, DepartmentId Having Avg(Salary) >50000 ORDER BY DepartmentId ,AverageSalaryofDepartment DESC
Which query will select all the employees whose salary over 80,000 and belong to DepartmentIds (3,4,5,6) A. SELECT EmployeeId, FirstName, LastName, Salary FROM HR.dbo.Employees (NOLOCK) WHERE Salary>80000 AND DepartmentId like (3,4,5,6) B. SELECT EmployeeId, FirstName, LastName, Salary FROM HR.dbo.Employees (NOLOCK) WHERE Salary>80000 AND DepartmentId = (3,4,5,6) C. SELECT EmployeeId, FirstName, LastName, Salary FROM HR.dbo.Employees (NOLOCK) WHERE Salary>80000 AND DepartmentId in (3,4,5,6) D. SELECT EmployeeId, FirstName, LastName, Salary FROM HR.dbo.Employees (NOLOCK) WHERE Salary>80000 AND Between 2 and 6
C. SELECT EmployeeId, FirstName, LastName, Salary FROM HR.dbo.Employees (NOLOCK) WHERE Salary>80000 AND DepartmentId in (3,4,5,6)
Which Transact-SQL fragment would you use to test a value in the Address column to see if it is null? A. Where Address = NULL B. Where Address = 'NULL' C. Where Address IS NULL D. Where Address = ''
C. Where Address IS NULL
Consider the following table schema − STUDENTS(StudentCode, FirstName, LastName, email, PhoneNo, DateofBirth, HonorsSubject, Grade); Which of the following query would correctly display the students' first name, last name, honors Subject and date of birth who are born after July 1st 1996, and before 30th June 1999. A. select FirstName, last name, HonorsSubject, DateofBirth from students where DateofBirth between '30-JUN-1999' and '01-JUL-1996'; B. select FirstName, last name, HonorsSubject, DateofBirth from students where DateofBirth between '01-JUL-1996' and '30-JUN-1999'; C. select FirstName, last name, HonorsSubject, DateofBirth from students where DateofBirth < '30- JUN-1999' AND DateofBirth > 01-JUL-1996'; D. select FirstName, last name, HonorsSubject, DateofBirth from students where DateofBirth > '30- JUN-1999' AND DateofBirth < 01-JUL-1996';
C. select FirstName, last name, HonorsSubject, DateofBirth from students where DateofBirth < '30- JUN-1999' AND DateofBirth > 01-JUL-1996';
Which of the following statements about the MAX function in SQL are true? A. Can be used on any datatype even CHAR and VARCHAR B. Returns the max value of the attribute. C. It is an aggregate function. D. All of the above statements are true
D. All of the above statements are true
Which of the following statements about the MAX function in SQL are true? A. Can be used on any datatype. B. Returns the max value of the attribute. C. It is an aggregate function. D. All of the above statements are true.
D. All of the above statements are true.
Which of the following is not a valid SQL Server type? A. DECIMAL B. FLOAT C. NUMERIC D. CHARACTER
D. CHARACTER
A column that generates numeric values is called A. Unique Column B. Numeric Column C. Integer Column D. Identity Column
D. Identity Column
Computer algorithms that automatically learn from and adapt in response to data defines which items? A. Data Visualization B. Programming Skills. C. Mathematical Ability. D. Machine Learning
D. Machine Learning
Which of the following is not true about modifying rows in a table? A. Existing rows in a table are modified using the UPDATE statement. B. You can update more than one row at a time. C. All the rows in a table are modified if you omit the WHERE clause. D. None of the above. All the previous statements are true.
D. None of the above. All the previous statements are true.
Which query will list (select) the DepartmentName,Department ID and the average salary of the department as "AverageSalaryofDepartment" per department from the Employees and Department Tables. ? order the result by department ID in ascending order and average Salary in a descending order. The output columns should be as follows: DepartmentName DepartmentId AverageSalaryofDepartment A. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId ORDER BY DepartmentId ASC,AVG(Salary)DESC B. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId GROUP BY DepartmentName, DepartmentId,AVG(Salary) ORDER BY DepartmentId ,AVG(Salary) DESC C. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId GROUP BY DepartmentName, DepartmentId ORDER BY DepartmentId ,AverageSalaryofDepartment D. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId GROUP BY DepartmentName, DepartmentId ORDER BY DepartmentId ,AverageSalaryofDepartment DESC
D. SELECT DepartmentName,DepartmentId,AVG(Salary) AS AverageSalaryofDepartment FROM dbo.Employees Emp(NOLOCK) INNER JOIN dbo.Departments DEP (NOLOCK) ON EMP.DepartmentId=DEP.DepartmentId GROUP BY DepartmentName, DepartmentId ORDER BY DepartmentId ,AverageSalaryofDepartment DESC
Which of the following queries would display names of all the students whose honors subject is English with grade over 80, or honors subject is Spanish with grade over 75? A. SELECT FirstName, LastName from students where (HonorsSubject= 'English' or HonorsSubject='Spanish') and (Grade> 80 or Grade >75) B. SELECT FirstName, LastName from students where (HonorsSubject= 'English' or Grade>80) AND(HonorsSubject= 'Spanish' or Grade>75) C. SELECT FirstName, LastName from students where (HonorsSubject= 'English' or Grade>80) OR (HonorsSubject= 'Spanish' or Grade>75) D. SELECT FirstName, LastName from students where (HonorsSubject= 'English' and Grade>80) OR (HonorsSubject= 'Spanish' and Grade>75)
D. SELECT FirstName, LastName from students where (HonorsSubject= 'English' and Grade>80) OR (HonorsSubject= 'Spanish' and Grade>75)
Which of the following query would display all the students with HonorsSubject in 'English' OR 'Spanish' A. SELECT StudentCode, FirstName, LastName from students where HonorsSubject = 'English' AND 'Spanish' B. SELECT StudentCode, FirstName, LastName from students where HonorsSubject = ('English' ,'Spanish') C. SELECT StudentCode, FirstName, LastName from students where HonorsSubject IN ('English' AND 'Spanish') D. SELECT StudentCode, FirstName, LastName from students where HonorsSubject IN('English','Spanish')
D. SELECT StudentCode, FirstName, LastName from students where HonorsSubject IN('English','Spanish')
Which is not true about triggers A. trigger is a database object that is attached to a table. B. It contains special instructions that are executed when important events, such as inserting or updating records in a table happen. C. Two items define a trigger on a table: a stored procedure and an event, such as inserting a record that invokes its execution. D. Triggers will not insert data in any table.
D. Triggers will not insert data in any table.
Which of the following query would display the full name of a student (First name Last Name) with a column heading "Name" from Student table. A. select FirstName, LastName as 'Name' from students; B. SELECT FirstName, LastName from students as 'Name' C. select Name from students; D. select (FirstName +' '+ LastName) as 'Name' from students;
D. select (FirstName +' '+ LastName) as 'Name' from students;
