Implementing SQL Queries Using MySQL Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

MySQL supports Structured Query Language (SQL) queries to handle data. You can use queries in MySQL to create databases and tables, insert data into tables, and retrieve data from the tables. The SQL queries are short and single line statements. There may be multiple statements in a single query.

In this session, you will learn to write queries to view tables in a database and modify the database. You will also use queries to retrieve data from a table and modify the table structure. In addition, you will learn to delete a table from the database.

Viewing and Altering Databases on the File System

While working with databases, you may need information about the number of databases present in the server, the names and other details of these databases, or you may need details about the structure of any database. You may also have to modify the structure of the database. MySQL enables you to view and alter an existing database on the file system through queries.

Viewing Information about Databases on the File System

Before executing any query in MySQL, you can list the databases that are managed by the server. You will use the SHOW command to display the list of databases and index keys along with the privileges. The SHOW command provides detailed information about databases, tables, columns, or status information about the server.

To view a list of databases present on the server, use the following syntax:

SHOW DATABASES;

where,

SHOW - displays the object specified in the clause

DATABASES - displays the databases present in the instance of MySQL

Figure 4.1 displays the output of the SHOW DATABASES command.

Figure 4.1: SHOW DATABASES Command

Figure 4.1 lists all the databases present on the server. It displays the total number of databases in the form of number of rows. In addition, it also displays the time taken to execute the command in the form of seconds.

To view a list of databases that begin or contain specified characters in the database name, use the following syntax:

SHOW DATABASES [LIKE <condition>];

where,

SHOW DATABASES - displays the list of databases present on the instance of MySQL

LIKE <condition>- contains conditions that must be satisfied before

displaying the list of databases

The LIKE clause can use conditions with wildcard characters such as '%' and '_'. This clause is optional. The '%' represents any string of zero or more characters. The '_' represents any single character.

For example, to view all databases that begin with the letter 'E', enter the following command at the command prompt:

SHOW DATABASES LIKE 'E%';

Figure 4.2 displays the output of the command. The output lists all the databases starting with the letter 'E', present on the server.

Figure 4.2: Using LIKE clause with SHOW DATABASES

To view a list of tables in a database, use the following syntax:

SHOW TABLES [FROM database_name] [LIKE <condition>];

where,

SHOW TABLES - displays the tables from the selected database

FROM database_name - displays the tables from the database specified in the clause

The FROM clause enables you to specify the database name for which tables are to be listed. You can use this keyword when you have not activated a database from the server with the USE command.

LIKE <condition> - displays the tables from the database based on the

condition specified in the clause

For example, to view the tables of the EMPLOYEE database that has been set as current database through the USE command, enter the following command at the command prompt:

SHOW TABLES;

Figure 4.3 displays the output of the command.

Figure 4.3: Tables in the EMPLOYEE Database

Figure 4.3 displays all the tables present in the EMPLOYEE database.

You must select the database with USE command before displaying the tables using the command shown in figure 4.3.

Note: You can also use 'mysqlshow database_name' command for displaying all the tables in a database.

To view a list of tables in a database when the database has not been selected earlier through the USE command, enter the following command at the command prompt:

SHOW TABLES FROM EMPLOYEE;

Figure 4.4 displays the output of the command.

Figure 4.4: Listing the Tables in EMPLOYEE database

You will use the FROM keyword here because the EMPLOYEE database has not been activated with the USE command before displaying the tables.

To view tables that begin with the letter 'E' from the EMPLOYEE database, enter the following command at the command prompt:

SHOW TABLES FROM EMPLOYEE LIKE 'E%';

Figure 4.5 displays the output of the command.

Figure 4.5: Listing Tables Beginning with 'E'

Figure 4.5 displays all the tables present in the EMPLOYEE database starting with letter 'E' and any number of characters after the character E. The LIKE keyword is used to display only those tables, which matches with the specified letter.

To view the column structure of a table from the database, use the following syntax:

SHOW COLUMNS FROM table_name [FROM database_name] [LIKE clauses];

where,

table_name - specifies the name of the table

COLUMNS - displays the columns from the specified table

table_name - specifies the name of the table that contains the columns

database_name - specifies the name of the database

LIKE - specifies conditions, if any

For example, to view the column structure of the EMP_DETAILS table, enter the following command at the command prompt:

SHOW COLUMNS FROM EMP_DETAILS;

Figure 4.6 displays the output of the command. Here, the database name is omitted because the EMPLOYEE database is the current database.

Figure 4.6: Columns of EMP_DETAILS

Figure 4.6 displays the columns present in the EMP_DETAILS table.

Table 4.1 lists the description of each of columns displayed in figure 4.6.

Column

Description

FIELD

Indicates column name

TYPE

Indicates the data type for a column

NULL

Specifies that the column can contain empty values

KEY

Specifies if the column is indexed

DEFAULT

Specifies the default value of the column

EXTRA

Specifies additional characteristics for the columns

Table 4.1: Detailed Information of Columns from SALARY_DETAILS

To display the same output with the EMPLOYEE database specified explicitly, enter the following command at the command prompt:

SHOW COLUMNS FROM SALARY_DETAILS FROM EMPLOYEE;

Figure 4.7 displays the output of the command.

Figure 4.7: Columns of SALARY_DETAILS with database specified

Figure 4.7 displays the columns present in the SALARY_DETAILS table along with the information about each of the columns. The first FROM keyword specifies the table to use for retrieving data. The second FROM keyword is used because you have not selected the database earlier with the USE command. Here, it specifies EMPLOYEE as the database in which the table SALARY_DETAILS is present.

Similarly, to view columns that start with the letter 'H' of the SALARY_DETAILS tables from the EMPLOYEE database, enter the following command at the command prompt:

SHOW COLUMNS FROM SALARY_DETAILS LIKE 'H%';

Figure 4.8 displays the output of the command.

Figure 4.8: Columns of SALARY_DETAILS starting with H

Figure 4.8 displays the columns present in the SALARY_DETAILS table starting with letter 'H'. You can use the LIKE keyword to display only those columns that start with letter 'H'.

A database index is a data structure that speeds up retrieval operations on a table. Indexes can be created using one or more columns of a database table. They enable faster searches and efficient organization of data.

To view the index in a table from a database, use the following syntax:

SHOW INDEX FROM table_name [FROM database_name];

where,

SHOW INDEX - displays the index information of the table specified in the table_name clause of the command

FROM table_name - specifies the name of the table to retrieve the index

FROM database_name - specifies the name of the database where the table exists

The SHOW INDEX command displays index information as shown in table 4.2.

Column

Description

Table

Displays name of the table

Non_unique

Displays 0 if the index cannot contain duplicate value

Key_name

Displays index name

Seq_in_index

Displays sequence number of columns in index, starting with 1

Column_name

Displays column name

Collation

Displays the sorting order of columns in the index

Cardinality

Displays number of unique values in the index

Sub_part

Displays number of indexed characters if the column is partly indexed

Table 4.2: Indexes on a Table

For example, to view the index in the EMP_DETAILS table of the EMPLOYEE database, enter the following command at the command prompt:

SHOW INDEX FROM EMP_DETAILS FROM EMPLOYEE;

Figure 4.9 displays the output of the command.

Figure 4.9: Index columns of EMP_DETAILS

Alternatively, you can use the following syntax:

SHOW INDEX FROM database_name.table_name

where,

SHOW INDEX - displays the table index

database_name - specifies the name of the database that contains the table

table_name - specifies the name of the table for which you want to display the index

For example, to display index keys of EMP_DETAILS from EMPLOYEE database, enter the following command at the command prompt:

SHOW INDEX FROM EMPLOYEE.EMP_DETAILS;

This command is more compact and concise.

To view the server status, use the following syntax:

SHOW STATUS;

where,

SHOW - displays the object specified in the clause

STATUS - displays information about the server

Figure 4.10 displays the output of the command.

Figure 4.10: Server Status

Note: The numbers and format may be different in various servers.

To view the values of the system variables, use the following syntax:

SHOW VARIABLES;

Figure 4.11 displays the output of the command.

Figure 4.11: System Variables

If the default values are unsuitable, set the values of the variables using the command-line options when mysqld starts or at runtime with the SET statement.

Note: The list of names and values may be different for your server

You can also view the system variable information using the mysqladmin command.

Similarly, to view only the variables that match a specified condition use the following syntax:

SHOW VARIABLES [LIKE <condition>];

For example, to display the variables that start with HAVE, enter the following command at the command prompt:

SHOW VARIABLES LIKE 'HAVE%';

Figure 4.12 displays the output of the command.

Figure 4.12: Server Variables starting with HAVE

To view the running threads or processes, use the following syntax:

SHOW [FULL] PROCESSLIST;

You must have SUPER privileges to view all the threads. The user accounts that do not have SUPER privileges can view only their threads.

Note: If the [FULL] option is not specified, only the first 100 characters of each query are shown.

The SHOW TABLE STATUS command is similar to the SHOW TABLE command except that it displays more information about each table. The syntax for SHOW TABLE STATUS command is as follows:

SHOW TABLE STATUS [FROM database_name] [LIKE <condition>];

where,

SHOW TABLE STATUS - displays information about tables in the database

FROM database_name - enables you to specify the database to select tables. You can

use the FROM clause if you have not activated the database with the USE command.

LIKE <condition> - specifies one or more conditions to be satisfied before displaying

the table information

Table 4.3 lists the information returned by the SHOW TABLE STATUS command.

Column

Description

Name

Displays the name of the table

Type

Displays type of table

Row_format

Displays storage format of the row (such as fixed, dynamic, or compressed)

Rows

Displays number of rows

Avg_row_length

Displays average row length

Data_length

Displays length of the data file

Max_data_length

Displays max length of the data file

Index_length

Displays length of the index file

Data_free

Displays number of allocated bytes but not used.

Auto_increment

Displays next auto increment value

Create_time

Displays the when the table was created

Update_time

Displays the date of last updation of data file

Check_time

Displays when the table was last checked

Comment

Displays the comments used when creating the table

Table 4.3: Information Returned by SHOW TABLE command

For example, to view the status of all the tables of the EMPLOYEE database, enter the following command at the command prompt:

SHOW TABLE STATUS FROM EMPLOYEE;

Figure 4.13 displays the output of the command.

Figure 4.13: Status of Tables in EMPLOYEE Database

To view a list of grants that are assigned for a user, use the following syntax:

SHOW GRANTS FOR user;

where,

SHOW - displays information specified in the clause

GRANTS - displays privileges or account rights

FOR - specifies the object to display the privileges

user - specifies the type of object for which privileges are to be displayed

For example, to display the rights granted to the root user, enter the following command at the command prompt:

SHOW GRANTS FOR root@localhost;

Figure 4.14 displays the output of the command. The output shows the list of grants for the root user.

Figure 4.14: Rights granted to root user

Altering Database on the File System

Alteration of a database involves making changes to the database. You can make changes to the data in the database, as well as the database structure. MySQL provides the ALTER DATABASE command to modify the global characteristics or attributes of a database stored in the db.opt file of the database directory. You will use the CHARACTER SET clause to modify the default database character set. In addition, you will use the COLLATE clause to modify the default database collation. A collation in MySQL database is a set of rules used in data comparisons.

The syntax for modifying the character set of a database is:

ALTER DATABASE database_name DEFAULT CHARACTER SET charset_name;

where,

ALTER DATABASE - edits the database

database_name - specifies the name of the database on which you need to make

changes

DEFAULT CHARACTER SET - specifies the default character set for the database

charset_name - specifies the type of character set for the database

For example, to modify the character set of the database, create a temporary database named TEST and then alter the database by entering the following command at the command prompt:

ALTER DATABASE TEST DEFAULT CHARACTER SET swe7;

Figure 4.15 displays the output of the command.

Figure 4.15: ALTER Database

The syntax to modify the collation of a database is:

ALTER DATABASE database_name DEFAULT COLLATE collation_name;

Note: You must have ALTER privileges to use the ALTER DATABASE command.

Retrieving Data using SELECT, FROM, DISTINCT and WHERE clauses

A table contains structured data in the form of rows and columns. The table must contain data to display. You cannot view the contents of a table if it does not contain data. Therefore, you must insert data before retrieving data from tables. MySQL provides the INSERT command to add data to the table. The syntax for INSERT command is:

INSERT INTO table_name {VALUES | VALUE} (value1, value2…);

where,

INSERT INTO - adds a new record to the table

table_name - specifies the name of the table to add the record

{VALUES | VALUE}- any one of these two may be used to specify the values and either

may be used for a single values list or multiple lists

value1 - specifies the data that will be added to the column

Figure 4.16 displays the insertion of records in the table.

Figure 4.16: INSERT Values

Figure 4.16 displays the method of inserting values in the table. The character value is specified with single quotation mark before and after the string.

You can use the SELECT command to retrieve data from one or more tables. The SELECT command has two clauses:

FROM - specifies the table name whose records are to be retrieved

WHERE - specifies the condition, based on which the records are retrieved. This clause is optional.

The syntax for retrieving all the records of a table is:

SELECT [*] FROM table_name;

where, '*' displays all the columns of the specified table.

For example, to view all records of the EMP_DETAILS table, enter the following command at the command prompt:

SELECT * FROM EMP_DETAILS;

Figure 4.17 displays the output of the command.

Figure 4.17: Displaying All the Records

You can use the DISTINCT keyword in the SELECT command to display columns that do not contain duplicate data. The DISTINCT keyword displays only those rows containing unique values in the specified column.

To display unique information from columns using DISTINCT keyword with SELECT command, use the syntax:

SELECT DISTINCT column_name FROM table_name;

where,

SELECT - displays data from the table

DISTINCT - is the keyword that displays unique information from columns. MySQL does not display duplicate records when you use this clause.

column_name - specifies the name of the column

table_name - specifies the name of the table where the column exists

This command will display the unique records from the columns specified in the column_name keyword.

For example, to display only the unique department names from the EMP_DEPARTMENT table, enter the following command at the command prompt:

SELECT DISTINCT D_NAME FROM EMP_DEPARTMENT;

Figure 4.18 displays the output of the command.

Figure 4.18: Displaying Unique Information

The SELECT command also enables you to view specific columns of a table.

The syntax for viewing only specific columns of a table is:

SELECT column_name1, column_name2 FROM table_name;

where,

SELECT - displays the information

column_name1 - specifies the name of the column to be retrieved

column_name2 - specifies the name of the column to be retrieved

table_name - specifies the name of the table that contains the columns

This command will display only those columns that are specified in the column_name clause of the command.

For example, to view the E_FNAME, E_LNAME, and E_ADDRESS columns of the EMP_DETAILS table, enter the following command at the command prompt:

SELECT E_FNAME, E_LNAME, E_ADDRESS FROM EMP_DETAILS;

Figure 4.19 displays the output of the command.

Figure 4.19: Displaying Specific Columns

Figure 4.19 displays the first name, last name, and address of all employees from the EMP_DETAILS table.

You can also use the SELECT command to retrieve computed rows without reference to any table.

SELECT 1+1;

Figure 4.20 displays the output of the command.

Figure 4.20: Computed Result

To perform arithmetic operations using the SELECT command, consider the following examples:

SELECT GROSS_SAL + 1000 FROM SALARY_DETAILS;

Figure 4.21 displays the output of the command.

Figure 4.21: Computed Gross Salary of Employees

Figure 4.21 displays the calculated sum of gross salary and 1000 in the GROSS_SAL column for each of the rows of the table.

4.3.1 SELECT command using WHERE clause

The WHERE clause contains one or more conditions that must be satisfied before the query retrieves a row. It searches for data according to the condition specified in the query and narrows the selection of data. The WHERE clause uses logical and conditional operators in the query.

The syntax to specify a condition using the WHERE clause is:

SELECT * FROM table_name WHERE <condition to satisfy>;

where,

SELECT - retrieves the data

* - specifies to retrieve all the columns of the table

table_name - specifies the name of the table

WHERE - specifies the clause to filter data

<condition to satisfy> - contains conditions to satisfy before retrieving and displaying data

For example, to view the records of EMP_SALARY table, where the basic salary is greater than 2500, enter the following command at the command prompt:

SELECT * FROM EMP_SALARY WHERE BASIC_SALARY > 2500;

Figure 4.22 displays the output of the command.

Figure 4.22: Displaying Records Using WHERE Clause

Similarly, to view the records of the EMP_DETAILS table where the last name is NORTON, enter the following command at the command prompt:

SELECT * FROM EMP_DETAILS WHERE E_LNAME = 'NORTON';

Figure 4.23 displays the output of the command.

Figure 4.23: Displaying Records Using WHERE clause With Text

Figure 4.23 displays all records from EMP_DETAILS table that have the last name as 'NORTON'.

To view all the fields of the EMP_DETAILS table, where the address of the employee is TROY or the address of employee is NEW JERSEY, enter the following command at the command prompt:

SELECT * FROM EMP_DETAILS WHERE E_ADDRESS = 'TROY' OR E_ADDRESS = 'NEW JERSEY';

Figure 4.24 displays the output of the command.

Figure 4.24: Displaying Records Using WHERE Clause

Modifying Table Definitions Using ALTER Command

MySQL provides the ALTER TABLE command to modify the structure of a table. You can add or delete columns, rename columns or the table, create or destroy indexes, and modify the column type.

The ALTER TABLE command creates a temporary copy of the original table, on which the alteration is performed. MySQL alters the temporary copy of the table, deletes the original copy, and renames the temporary copy of the table. MySQL uses this feature as a security measure to prevent data loss in case the table modification fails.

The syntax for altering a table is:

ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec...]

where,

ALTER - specifies to edit the object

IGNORE - checks for duplicates on the unique keys in the new table. If MySQL identifies a duplicate record on the unique keys, it returns only the first row and deletes the duplicates.

TABLE - specifies the type of object to edit

table_name - specifies the name of the table

alter_spec - contains modification information

Note: If the [IGNORE] clause is not specified, the copy is aborted and is rolled back if the duplicate-key errors occur.

The syntax to add a column in the existing table is:

ALTER TABLE table_name ADD [COLUMN] create_definition [FIRST | AFTER column_name]

where,

table_name - specifies the name of the table to modify

ADD [COLUMN] - appends a new column to the table

create_definition - defines the column type in a table creation

FIRST | AFTER column_name - specifies the location of the new column in the table

For example, to add a column CITY to the EMP_DETAILS table for entering the name of the city of employees, enter the following command at the command prompt:

ALTER TABLE EMP_DETAILS ADD (CITY CHAR (10));

Figure 4.25 displays the output of the command.

Figure 4.25: ALTER TABLE Command

The syntax to add an index key to a column of a table is:

ALTER TABLE table_name ADD INDEX [index_name] (index_column_name...)

where,

ALTER TABLE - specifies to modify the table

table_name - specifies the name of the table

ADD INDEX - appends an index to the table

index_name - specifies a name for the index

index_column_name - specifies the column in the table to index

This command adds an index to the table and indexes the specified column.

For example, to add an index on column E_ID on EMP_DETAILS table, enter the following command at the command prompt:

ALTER TABLE EMP_DETAILS ADD (INDEX (E_ID));

Figure 4.26 displays the output of the command.

Figure 4.26: Adding an Index Key

The syntax to add a primary key to the column of a table is:

ALTER TABLE table_name ADD PRIMARY KEY (index_column_name...)

where,

ALTER TABLE - modifies the table structure

table_name - specifies the name of the table

ADD PRIMARY KEY - appends a primary key to the table

index_column_name - specifies the name of the column to use as an index for the

primary key

This command adds a primary key to the specified indexed column in the table.

For example, to add a primary key on column E_ID on EMP_DETAILS table, enter the following command at the command prompt:

ALTER TABLE EMP_DETAILS ADD (PRIMARY KEY (E_ID));

Figure 4.27 displays the output of the command.

Figure 4.27: Adding Primary Key

To modify a column definition, use the following syntax:

ALTER TABLE table_name MODIFY [COLUMN] create_definition

where,

ALTER TABLE - specifies to edit the table

table_name - specifies the name of the table

MODIFY [COLUMN] - specifies to change the column structure

create_definition - specifies the new rules for the column

For example, to modify DESIGNATION column from CHAR(50) to CHAR(20) data type of EMP_DEPARTMENT table, enter the following command at the command prompt:

ALTER TABLE EMP_DEPARTMENT MODIFY DESIGNATION CHAR(20);

Figure 4.28 displays the output of the command.

Figure 4.28: Modifying a Column

To drop a column from a table, use the following syntax:

ALTER TABLE table_name DROP [COLUMN] column_name

where,

ALTER TABLE - modifies the table structure

table_name - specifies the name of the table to modify

DROP [COLUMN] - removes the column from the table

column_name - specifies the name of the column to remove from the table

This command removes a column from the table.

For example, to remove the column CITY from EMP_DETAILS table, enter the following command at the command prompt:

ALTER TABLE EMP_DETAILS DROP COLUMN CITY;

Figure 4.29 displays the output of the command.

Figure 4.29: Deleting a Column

To drop a primary key of a column from the table, use the following syntax:

ALTER TABLE table_name DROP PRIMARY KEY

where,

ALTER TABLE - edits the table structure

table_name - specifies the name of the table to modify

DROP PRIMARY KEY - removes the primary key from the table

Note: The DROP PRIMARY KEY option deletes the primary index. If primary index does not exist, it drops the first UNIQUE index in the table.

For example, to remove primary key constraint from EMP_DETAILS table, enter the following command at the command prompt:

ALTER TABLE EMP_DETAILS DROP PRIMARY KEY;

To change the name of a table, use the following syntax:

ALTER TABLE table_name RENAME new_table_name

where,

ALTER TABLE - edits the table structure

table_name - specifies the name of the table to modify

RENAME - changes the name of the table

new_table_name - specifies the new name for the table

For example, to rename the table from SALARY_DETAILS to EMP_SALARY, enter the following command at the command prompt:

ALTER TABLE SALARY_DETAILS RENAME EMP_SALARY;

Figure 4.30 displays the output of the command.

Figure 4.30: Renaming a Table

Following are some of the points to be remembered while working with ALTER TABLE command:

MySQL deletes the index values of columns when you drop columns from a table. MySQL also removes the index when you drop or delete all indexed columns.

If you use ALTER TABLE to change a column specification and if DESCRIBE table_name shows that the column specification has not changed, then it is possible that your modification is ignored because the size of the column to be changed may be greater than or less than the required size length. For example, you try to change VARCHAR column to CHAR, MySQL may not allow the modification to CHAR if the respective column length is not less than four.

You can use several ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE command.

Note: To use ALTER TABLE, you will require SELECT, INSERT, DELETE, UPDATE, CREATE, and DROP privileges on the table.

Deleting Table Definitions Using DROP Command

MySQL provides the DROP TABLE command to remove or delete tables from a database. This command removes table definition, data, indexes, triggers, constraints, and permissions for that table.

Note: The DROP command should be used carefully because it removes all the data from a table along with table definitions.

The syntax for the DROP command is:

DROP TABLE [IF EXIST] table_name[table_name1,…][RESTRICT | CASCADE];

where,

table_name - specifies the name of the table to delete

IF EXIST - prevents error occurrence while executing the command if the table does not

exist

RESTRICT - specifies that if any dependencies exist, MySQL will not delete the table, if

dependencies exist. If MySQL, encounters a dependency, it returns an error

but does not delete the table.

CASCADE - specifies to remove dependencies before deleting the table

For example, to remove a table named SAMPLE from the EMPLOYEE database, enter the following command at the command prompt:

DROP TABLE SAMPLE;

Figure 4.31 displays the output of the command.

Figure 4.31: Deleting a Table

You can use the IF EXIST clause with DROP command if you are unsure about the existence of table in the database.

Note: The DROP TABLE command cannot be used to delete tables that are referencing FOREIGN KEY. You will have to create a sample table to execute this command. Do not drop the existing tables from the database.

Summary

MySQL provides the SHOW command to view the list of databases and tables on the server, display the server status, and user account privileges.

The ALTER command enables modification of the characteristics or attributes of a table or a database.

The CHARACTER SET clause defines the default character set of a database.

The default collation of a database can be defined using the COLLATE clause.

The SELECT command is used to retrieve data from a table.

The FROM clause in the SELECT command specifies the table name whose records are to be retrieved.

The WHERE clause in the SELECT command specifies conditions for retrieving data from the table. MySQL displays only those records that satisfy the conditions in the WHERE clause.

Conditional and logical operators can be used with SELECT command to retrieve data after satisfying data retrieval conditions.

The DROP command removes or deletes a table from a database. This command removes all the data and the table definition from the database.

MySQL provides the IF EXIST option in the DROP command to check the existence of the table before you delete it.

Check Your Progress

You can view the columns of a table using the _________________ command.

SELECT

VIEW

SHOW

USE

MySQL displays only the first 100 characters of each query in the SHOW PROCESSLIST command, if you do not specify the ___________ clause.

STATUS

VARIABLE

FULL

INDEX

Which of the following is similar to the SHOW TABLE command except that it displays detailed information about each table?

SHOW STATUS

SHOW TABLE STATUS

SHOW TABLES

SHOW VARIABLES

The ______________ command performs actions on temporary copy of original database.

MODIFY

ALTER

CHANGE

UPDATE

In which file of the database directory does MySQL store the global characteristics attributes of a database?

tables_priv

db

user

db.opt

Which of the following commands will display all the records of EMPLOYEE table?

SELECT ALL FROM EMPLOYEE;

SELECT COLUMNS FROM EMPLOYEE;

SELECT * FROM EMPLOYEE;

SELECT RECORDS FROM EMPLOYEE;

You will use the __________ option of the DROP command to display the dependencies of a table.

IF EXIST

RESTRICT

CASCADE

DROP TABLE

This page has been intentionally left blank.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.