MySQL Basics Flashcards
All questions are from https://www.sanfoundry.com/mysql-questions-answers-basic-database-terminology/ (97 cards)
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.
