Font size
WorksheetsSQLTest 01
Total questions: 56
Worksheet time: 29mins
In SQL, a view is a ......... table based on the result-set of an SQL statement.
REAL
SELF
VIRTUAL
The conceptual model is...
Dependant on hardware
Dependant on software
Dependant on both hardware and software
Independant on hardware and software
A collection of tables to represent both data and the relationships among is known as,.........
Entity relationship model
Relational Model
Object base data model
Semi structured data model
DBMS is a general purpose software system that facilitate the process of
Defining a database
Constructing a database
Manipulating database
Which is database language ?
C
C++
SQL
None
......... is the structure of the database.
Table
Relation
Schema
None
Schema is defined by
DML
DDL
DCL
DQL
DBMS is bridge between operating system and .............
User
Database administrator
Application program
None
An entity has set of ............ that describe it.
Attributes
Entity set
Relationships
None
.......... is the information about data.
Data
Metadata
Entity
Relations
Which level of abstraction describes what data are stored in the database ?
Physical level
View level
Abstraction level
Logical level
Which of the following is not disadvantage of DBMS ?
High Cost
Adequate backup
No redundancy
Complexity
Which of the following is structure of database ?
Table
Schema
Relation
None
Which SQL function is used to count the number of rows in a SQL query?
COUNT()
NUMBER()
SUM()
COUNT(*)
Which of the following SQL clauses is used to DELETE tuples from a database table?
DELETE
REMOVE
DROP
CLEAR
If you don’t specify ASC or DESC after a SQL ORDER BY clause, the following is used by default
ASC
DESC
There is no default value
None of the mentioned
With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” ends with an “a”?
SELECT * FROM Persons WHERE FirstName=’a’
SELECT * FROM Persons WHERE FirstName LIKE ‘a%’
SELECT * FROM Persons WHERE FirstName LIKE ‘%a’
SELECT * FROM Persons WHERE FirstName=’%a%’
What does the ALTER TABLE clause do?
The SQL ALTER TABLE clause modifies a table definition by altering, adding, or deleting table columns and/or constraints
The SQL ALTER TABLE clause is used to insert data into database table
THE SQL ALTER TABLE deletes data from database table
The SQL ALTER TABLE clause is used to delete a database table
The UPDATE SQL clause can _____________
update only one row at a time
update more than one row at a time
delete more than one row at a time
delete only one row at a time
How can you change “Hansen” into “Nilsen” in the “LastName” column in the Persons table?
UPDATE Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’
MODIFY Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
MODIFY Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’
UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
SQL query to find all the cities whose humidity is 95.
SELECT city WHERE humidity = 95
SELECT city FROM weather WHERE humidity = 95
SELECT humidity = 89 FROM weather
SELECT city FROM weather
SQL query to find the temperature in increasing order of all cities.
SELECT city FROM weather ORDER BY temperature
SELECT city, temperature FROM weather
SELECT city, temperature FROM weather ORDER BY temperature
SELECT city, temperature FROM weather ORDER BY city
Find the name of those cities with temperature and condition whose condition is either sunny or cloudy but temperature must be greater than 70.
SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ AND condition = ‘cloudy’ OR temperature > 70
SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ OR condition = ‘cloudy’ OR temperature > 70
SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ OR condition = ‘cloudy’ AND temperature > 70
SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ AND condition = ‘cloudy’ AND temperature > 70
The command to remove rows from a table ‘CUSTOMER’ is __________________
DROP FROM CUSTOMER
UPDATE FROM CUSTOMER
REMOVE FROM CUSTOMER
DELETE FROM CUSTOMER WHERE
Find the names of these cities with temperature and condition whose condition is neither sunny nor cloudy.
SELECT city, temperature, condition FROM weather WHERE condition NOT IN (‘sunny’, ‘cloudy’)
SELECT city, temperature, condition FROM weather WHERE condition NOT BETWEEN (‘sunny’, ‘cloudy’)
SELECT city, temperature, condition FROM weather WHERE condition IN (‘sunny’, ‘cloudy’)
SELECT city, temperature, condition FROM weather WHERE condition BETWEEN (‘sunny’, ‘cloudy’);
What will be the output of the query :
Select Substring('mysql application',3,3)
app
mysql
application
sql
What will be the output of the following query:
Select round(59999.99,-2);
59999
59900
60000
59990
Which function will be used to remove only the trailing spaces from a string?
ltrim()
rtrim()
trim()
all
Fill in the blank:
___________________ this clause is used to apply condition on all the rows of table.
Where
Having
Order By
Sort By
Difference between ALTER Table command and UPDATE command :
ALTER is DDL command and is used for modifying the schema of table
UPDATE is DML command and is
used for modifying the existing data of table
Using Alter command any value inside the table can be modified.
Using Update command columns of a table can be renamed.
Which clause is used when we want to use aggregate functions in conditions?
Where
Group By
Having
Both 2 and 3
Which statement allows us to add a record to a table?
Add To
Update To
Add Into
Insert Into
Normalisation is:
Removing all necessary data from a database
Organising a database to remove repeated entries and increase the accuracy of the data
Putting fields from different tables into one big database
An advantage of normalisation is:
The storage space needed for a normalised database is likely to be smaller
The process of normalising a database is very easy and doesn’t require any skill
It is possible to see previous details that have been changed, such as a customer’s address, as we can look back at an older entry
An entity is represented by what in a database?
Table
Row
Column
Cell
An attribute is represented by what in a database?
Table
Row
Column
Cell
What is a foreign key?
A unique identifier in a database
A primary key of one table that appears in another table
A field that should not be in a table and needs removing
What is a functional dependency?
when an non-key attribute is determined by a whole part of a COMPOSITE primary key.
When a key field is changed and it conflicts with another key
When a database function is dependant of the operation of another function to work correctly
When data is a database is repeated for different entities
What is a composite primary key?
A primary key that can be duplicated in the same table
An attribute that is a primary key in another table
A foreign key that is a primary key in more than one other table
A primary key made up from two or more attributes
A relation is in 1NF if it doesn't contain any ____________?
Determinants
Repeating columns
Null values in primary key fields
Functional dependencies
After being in 1NF, a table is in 2NF if it has...
only one primary key field
all of its fields are Whole Key Dependant
non-key fields that are functionally dependant
at least one repeating group
Which of the following statements fully describes data in 2NF:
When any repeating fields have been removed and the table is given a primary key
When all repeating entries of data are removed and the fields in each table are directly related to the primary key and no fields are present that are not related to each other
When all the fields in each table are directly related to the primary key
What is an atomic field?
A field that contains multiple items of data
A field that is repeated
A field that contains only one item of data
Problems in Relational Data Model are :
Anomalies
Information Redundancy
None of the above
3 types of anomalies are :
insert
delete
modification
None of the above
Information Redundancy is Duplication of data due to storing the same data multiple times.
True
False
A process for assigning attributes into a table and reduces data redundancy and helps eliminate the data anomalies that associated with poor database design is called as :
Normalization
Information Redundancy
Anomalies
The purposes of normalization are :
To ensure that the anomalies doesn’t occur
To reduce data or storage redundancy
To ensure easy for record searching/finding
All the above
Steps in Normalization are :
UNF- 1NF-2NF-3NF-BCNF
1NF-2NF-3NF-BCNF
2NF-3NF-BCNF
None of the above
"Constraint between two attributes or two sets of attributes."
The above statement is referring to :
Functional Dependecies
Transitive Dependencies
Choose three types of Functional Dependency
Full Functional Dependency
Partial Functional Dependency
Transitive Functional Dependency
None of the above
"Occurs when an attribute is functionally dependent on another non-key attribute."
The above statement is referring to :
Full Functional Dependency
Partial Functional Dependency
Transitive Dependencies
A disadvantage of normalisation is:
The data loses its integrity as some of it is removed in the normalisation process
The process of searching the database may be slower due to a higher demand on the central processing unit (CPU)
Removing redundant data means that links cannot be created between tables
A relation is in 1NF if it doesn't contain any ____________?
Determinants
Repeating groups
Null values in primary key fields
Functional dependencies
When designing a database you should ____.
make sure entities are in normal form before table structures are created
create table structures then normalize the database
only normalize the database when performance problems occur
consider more important issues such as performance before normalizing
A table in ____ contains no transitive dependencies.
1NF
2NF
3NF
none of the above
