MySQL Basics Flashcards
All questions are from https://www.sanfoundry.com/mysql-questions-answers-basic-database-terminology/
Which type of database management system is MySQL?
a) Object-oriented
b) Hierarchical
c) Relational
d) Network
Answer: c
Explanation: MySQL is a ‘relational’ DBMS. It is efficient at relating data in two different tables and joining information from them. Hierarchical and Network DBMS are based on parent-child relationships of records. Object-oriented DBMS use objects to represent models.
What is data in a MySQL database organized into?
a) Objects
b) Tables
c) Networks
d) File systems
Answer: b
Explanation: Since MySQL is an RDBMS, it’s data is organised in tables for establishing relationships. A table is a collection of rows and columns, where each row is a record and columns describe the feature of records.
MySQL is freely available and is open source.
a) True
b) False
Answer: a
Explanation: MySQL is free and open source. It’s source code is available for use and is freely downloadable. It includes the MySQL Server, the world’s most popular open source database, and MySQL Cluster, a real-time, open source transactional database.
What represents an ‘attribute’ in a relational database?
a) Table
b) Row
c) Column
d) Object
Answer: c
Explanation: Each column in a table represents a feature (attribute) of a record. Table stores the information for an entity whereas a row represents a record. Object has no relevance in an RDBMS.
What represents a ‘tuple’ in a relational database?
a) Table
b) Row
c) Column
d) Object
Answer: b
Explanation: Each row in a table represents a record. A tuple is a collection of attribute values that makes a record unique. A tuple is a unique entity whereas attribute values can be duplicate in the table.
How is communication established with MySQL?
a) SQL
b) Network calls
c) A programming language like C++
d) APIs
Answer: a
Explanation: SQL is the standard language for RDBMS systems like MySQL. SQL queries facilitate quick information retrieval from tables and other elementary operations required to maintain an RDBMS system.
What does ‘name’ represent in the following SQL code snippet?
CREATE TABLE student
(
name CHAR(30),
roll_num INT,
address CHAR(30),
phone CHAR(12)
);
a) A table
b) A row
c) A column
d) An object
Answer: c
Explanation: ‘name’, ‘roll_num’, ‘address’ and ‘phone’ are the attributes in the table ‘student’. The CREATE TABLE construct in SQL creates a table, assigns a name to it and its attributes, and specifies the type of fields used in the table.
Which is the MySQL instance responsible for data processing?
a) MySQL client
b) MySQL server
c) SQL
d) Server daemon program
Answer: b
Explanation: MySQL uses client-server architecture. The MySQL server program runs on the machine where databases are stored. SQL is a query language used for querying the tables and information retrieval.
The MySQL server used in its client/server architecture is _______________
a) mysqla
b) mysqlb
c) mysqlc
d) mysqld
Answer: d
Explanation: mysqld is the MySQL server program. It serves the incoming client requests by accessing the database. The others are not valid MySQL programs. MySQL implements a client/server architecture wherein mysqld is the server program.
In MySQL databases, the structure representing the organizational views of the entire databases is ____________
a) Schema
b) View
c) Instance
d) Table
Answer: a Explanation: The schema in a database gives a blueprint of the structure. A view is an object that can be generated with a query. A table is a collection of records. An instance is analogous to a class object.
Which of the following clauses is used to display information that match a given pattern?
a) LIKE
b) WHERE
c) IS
d) SAME
Answer: a
Explanation: The ‘LIKE’ clause filters information that match a given pattern. ‘WHERE’ clause selects information that is specified by a condition. ‘IS’ is used to match the exact condition specified.
What column names are displayed when this SQL command is executed?
SHOW COLUMNS FROM tbl_name LIKE ‘%name’;
a) suffixed with ‘name’
b) prefixed with ‘name’
c) suffixed with ‘%name’
d) prefixed with ‘%name’
Answer: a
Explanation: The wildcard ‘%’ is used to indicate that any number of characters can replace it. All column names that end in ‘name’ are displayed. Additional information of columns like type and size are listed.
MySQL server can operate in different SQL modes, depending on the value of the sql_mode system variable. Which mode changes syntax and behavior to conform more closely to standard SQL?
a) TRADITIONAL
b) ANSI
c) MSSQL
d) STRICT
Answer: b
ANSI - This mode changes syntax and behavior to conform more closely to standard SQL.
If you were building a table schema to store student grades as a letter (A, B, C, D, or F) which column type would be the best choice?
a) ENUM
b) OTEXT
c) VARCHAR
d) LONGTEXT
Answer: a
An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.
example
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
Which option of most MySQL command-line programs can be used to get a description of the program’s different options?
a) –options
b) ?
c) –help
d) -h
Answer: c
–help Display help message and exit
there is no shortcut key
MySQL uses environment variables in some of the programs and command-line operations. Which variable is used by the shell to find MySQL programs?
a) DIR
b) HOME
c) PATH
d) MYSQL_HOME
Answer: c
PATH Used by the shell to find MySQL programs.
There is no env variable used by MySQL called DIR. HOME The default path for the mysql history file is $HOME/.mysql_history. MYSQL_HOME The path to the directory in which the server-specific my.cnf file resides.
You must ensure the accuracy and reliability of the data in your database. You assign some constraints to limit the type of data that can go into a table. What type of constraints are you assigning?
a) row level
b) database level
c) column level
d) function level
Answer: c
Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
You need to export the entire database, including the database objects, in addition to the data. Which command-line tool do you use?
a) mysqlexport
b) mysqladmin
c) mysqldump
d) mysqld
Answer: c
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
mysqlexport - no such program
mysqladmin is a client for performing administrative operations. You can use it to check the server’s configuration and current status, to create and drop databases, and more. mysqladmin [options]
mysqld - the database server executable
When you have a subquery inside of the main query, which query is executed first?
a) The subquery is never executed. Only the main query is executed.
b) They are executed at the same time
c) the main query
d) the subquery
Answer: d
The subquery is called an Inner query and the main query is called the outer query. The inner query is executed first and then the main query gets executed. In case you have multiple subqueries then the innermost query gets executed first.
MySQL uses security based on___for all connections, queries, and other operations that users can attempt to perform.
a) administrator schema
b) encrypted algorithms
c) user settings
d) access control lists
Answer: d
MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers.
Which of the following is NOT the correct usage of a comment in a SQL statement for MySQL?
a) SELECT /* Author: jsai
Purpose: select contacts / contact_id, last_name, first_name
FROM contacts;
b) SELECT contact_id, last_name, first_name # jsai
FROM contacts;
c) SELECT contact_id, last_name, first_name / jsai
FROM contacts;
d) SELECT contact_id, last_name, first_name – jsai
FROM contacts;
Answer: c using # symbol is: # comment goes here using -- symbol is: -- comment goes here using /* and */ symbols is: /* comment goes here */
This is a correct example of a comment spanning multiple lines SELECT contact_id, last_name, first_name /* * Author: TechOnTheNet.com * Purpose: To show a comment that spans multiple lines in your SQL * statement in MySQL. */ FROM contacts;
Which of the following is NOT a string function
a) repeat
b) mid
c) reverse
d) truncate
Answer : d
The MySQL TRUNCATE function returns a number truncated to a certain number of decimal places.
The MySQL REPEAT function repeats a string a specified number of times. REPEAT( string, number )
The MySQL REVERSE function returns a string with the characters in reverse order. REVERSE( string )
The MySQL MID function allows you to extract a substring from a string. MID( string, start_position, length )
Assuming that we have a table called contacts in MySQL that is populated with the following data:
contact_id last_name website1 website2
1 Johnson techonthenet.com
2 Anderson
3 Smith TBD TBD
4 Jackson math.com minecraft.com
SELECT *
FROM contacts
WHERE website1 <=>website2;
what happens when we query using the following above query:
a) only row 3 is returned
b) all rows are returned
c) no rows are returned
d) rows 2 and 3 are returned
Answer : d
our query returns all rows from the contacts table where website1 is equal to website2, including those records where website1 and website2 are NULL values.
Because the = operator only compares non-NULL values, it is not safe to use with NULL values. To overcome this limitation, MySQL added the <=> operator to allow you to test for equality with both NULL and non-NULL values.
Assuming that we have a table called contacts in MySQL that is populated with the following data:
contact_id last_name website1 website2
1 Johnson techonthenet.com
2 Anderson
3 Smith TBD TBD
4 Jackson math.com minecraft.com
SELECT *
FROM contacts
WHERE last_name <> ‘Johnson’;
what happens when we query using the following above query:
a) the SELECT statement would return all rows from the contacts table where the last_name is not equal to Johnson.
b) the SELECT statement would return all rows from the contacts table where the last_name is equal to Johnson.
c) the SELECT statement would return all rows from the contacts table where the last_name is equal to Johnson and any any rows with NULL
d) an error would occur
Answer: a
In MySQL, you can use the <> or != operators to test for inequality in a query.
the SELECT statement would return all rows from the contacts table where the last_name is not equal to Johnson.
Which of the following statements will fail?
a) SELECT depart, MAX(salary) AS highest
FROM employees
GROUP BY department;
b) SELECT depart, MAX(salary) AS highest salary
FROM employees
GROUP BY department;
c) SELECT department, MAX(salary) “highest salary”
FROM employees
GROUP BY department;
d) SELECT department, MAX(salary) AS “highest”
FROM employees
GROUP BY department;
Answer: b
aliases are used to make the column headings in your result set easier to read.
if the alias_name did not include any spaces, we are not required to enclose the alias_name in quotes. However, it would have been perfectly acceptable to add quotes even if no spaces.
Since there are spaces in this alias_name, “highest salary” must be enclosed in quotes.
the AS keyword is optional, Most programmers will specify the AS keyword when aliasing a column name, but not when aliasing a table name. Whether you specify the AS keyword or not has no impact on the alias in MySQL.
The following command was entered in mysql shell
CREATE USER IF NOT EXISTS ‘test’@’localhost’ IDENTIFIED BY ‘Password’ PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Which of the following can be used to troubleshoot why the command failed?
a) show error;
b) show error();
c) show warning;
d) show warning();
Answer : c
SHOW WARNINGS [LIMIT [offset,] row_count]
you have to run it immediately after a query that had warnings attached to it.
show warning(); is incorrect syntax
SHOW ERRORS, shows only error conditions (it excludes warnings and notes),
The LOCAL capability for LOAD DATA does not need to be enabled explicitly.
a) True
b) False
Answer: a
Explanation: The ‘LOCAL’ capability for the ‘LOAD DATA’ statement does not need to be enabled explicitly. It can be controlled at build time and at runtime. This can be achieved in two ways.
The LOCAL capability for client library or LOAD DATA is enabled by ______________
a) –enabled-local-file
b) –enable-local-infile
c) –enable-global-file
d) –enable-local-file
Answer: b
Explanation: At build time LOCAL capability for the client library or LOAD DATA can be enabled or disabled by default. The ‘–enable-local-infile’ or ‘–disable-local-infile’ option is used when running configure.
What is the most important configurable resource for MyISAM?
a) key cache
b) memory cache
c) time cache
d) speed cache
Answer: a
Explanation: For the index processing, ‘MyISAM’ manages its own key cache, which is the most important configurable resource for the MyISAM storage engine. It is used for index based retrievals and sorts.
How many options can be used to control LOCAL capability at runtime?
a) 0
b) 1
c) 2
d) 3
Answer: c
Explanation: At runtime, the server can be started with the ‘–local-infile’ or ‘–skip-local-infile’ options to enable or disable ‘LOCAL’ capability on the server side. It can be enabled at build time too.
‘x’ in the following MySQL statement is ____________
DELETE FROM x USING x LEFT JOIN y ON x.col = y.col;
a) column name
b) table name
c) server name
d) database name
Answer: b
Explanation: The ‘DELETE’ operation is being performed in the statement. The table names are ‘x’ and ‘y’. The column name is ‘col’. The rows from left join of x and y get deleted according to the condition given.
The mode used to turn off the special meaning of backslash and treat it as an ordinary character is _____________
a) NO_ESCAPES_SLASH
b) NO_ESCAPES_BACKSLASH
c) NO_BACKSLASH_ESCAPES
d) NO_BACKSLASH_ESCAPE
Answer: c
Explanation: To turn off the special meaning of backslash in MySQL, and treat it as an ordinary character, the SQL mode named NO_BACKSLASH_ESCAPES is enabled. The escape sequence is treated as characters.
What is the synonym for CHARACTER SET?
a) CSET
b) CHSET
c) CHARSET
d) CHCSET
Answer: c
Explanation: ‘CHARACTER SET’ can be abbreviated into ‘CHARSET’ and can be used in the same contexts and statements where ‘CHARACTER SET’ is used. ‘charset’ is the server-supported character set.
The option that executes all SQL statements in a SQL script irrespective of the number of errors is ______________
a) –ensure
b) –force
c) –violent
d) –run
Answer: b
Explanation: If SQL queries in a file are run using mysql in batch mode, mysql either quits after the first error. If the –force option is specified all the queries are executed indiscriminately.
Which keyword is the synonym for DATABASE?
a) TABLE
b) OBJECT
c) DB
d) SCHEMA
Answer: d
Explanation: In any statement where the word ‘DATABASE’ occurs, the keyword ‘SCHEMA’ can be used as a synonym in place of it. In the literal sense, SCHEMA refers to the structure of the database.
It is not required to have an access privilege for a database before selecting it with ‘USE’.
a) True
b) False
Answer: b
Explanation: In order to select a database as the default database for the MySQL server using the ‘USE’ statement, some access privilege for the database needs to be granted or attained.
The file created by the server to store the database attributes is __________
a) db.otp
b) dp.zip
c) db.opt
d) db.cls
Answer: c
Explanation: Whenever a database is created in MySQL, the MySQL server creates a directory with the same name as the database. It creates the file db.opt to store the attributes.
Which of the following is the correct way to run a os command from the mysql shell?
a) system ls -l
b) ! ls -l
c) both of the above
d) neither of the above
Answer: c
the command system or ! can be used to run os command from database command line.
