C170 58 Question MultChoice OA StudyGuide - SQL Commands

WGU C170| 58 QUESTIONS MULTCHOICE OA STUDY GUIDE WITH
COMPLETE SOLUTIONS| SQL COMMANDS

SQL Commands

Please take a look at this list and review any that seem unfamiliar:

CREATE TABLE statements and data type assignments

create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");

Here are the most common Data types:

char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size) Variable-length character string. Max size is specified in parenthesis.
number(size) Number value with a max number of column digits specified in parenthesis.
date Date value
Number value with a maximum number of digits of "size" total, with a maximum number of "d"
number(size,d)
digits to the right of the decimal.

CREATE TABLE … LIKE

Use CREATE TABLE ... LIKE to create an empty table based on the definition of another table, including any column
attributes and indexes defined in the original table:
CREATE TABLE new_tbl LIKE orig_tbl;

CREATE TABLE ... LIKE creates a new table as an empty copy of the original one. It copies the original table structure
exactly, so that each column is preserved with all of its attributes. The index structure is copied as well. However,
the new table is empty, so to populate it a second statement is needed (such as INSERT INTO ... SELECT). Also,
CREATE TABLE ... LIKE cannot create a new table from a subset of the original table's columns, and it cannot use
columns from any other table but the original one.

To use CREATE TABLE ... LIKE for creating an empty copy of an existing table, write a statement like this:

CREATE TABLE new_tbl_name LIKE tbl_name;

CREATE TABLE … SELECT

To create one table from another, add a SELECT statement at the end of the CREATE TABLE statement:
CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

CREATE TABLE ... SELECT creates a new table from the result of an arbitrary SELECT statement. By default, this
statement does not copy all column attributes such as AUTO_INCREMENT. Nor does creating a table by selecting
data into it automatically copy any indexes from the original table, because result sets are not themselves indexed.
On the other hand, CREATE TABLE ... SELECT can both create and populate the new table in a single statement. It
also can create a new table using a subset of the original table and include columns from other tables or columns
created as the result of expressions.

,CREATE TABLE ... SELECT also can create new tables that don't contain exactly the same set of columns in an
existing table. You can use it to cause a new table to spring into existence on the fly to hold the result of an
arbitrary SELECT query. This makes it exceptionally easy to create a table fully populated with the data in which
you're interested, ready to be used in further statements. However, the new table can contain strange column
names if you're not careful. When you create a table by selecting data into it, the column names are taken from the
columns that you are selecting.

To create an empty copy of a table and then populate it from the original table, use CREATE TABLE ... LIKE followed
by INSERT INTO ... SELECT:

CREATE TABLE new_tbl_name LIKE tbl_name;
INSERT INTO new_tbl_name SELECT * FROM tbl_name;

ALTER TABLE (what is it used for)

ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy
indexes, change the type of existing columns, or rename columns or the table itself. You can also change
characteristics such as the storage engine used for the table or the table comment.

ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
[partition_options]

ALTER TABLE (examples)

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

DROP TABLE

DROP TABLE removes one or more tables. You must have the DROP privilege for each table.

DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]

CREATE VIEW

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables
in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming
from one single table.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

, ALTER VIEW

This statement changes the definition of a view, which must exist. The syntax is similar to that for CREATE VIEW see
Section 13.1.21, “CREATE VIEW Statement”). This statement requires the CREATE VIEW and DROP privileges for the
view, and some privilege for each column referred to in the SELECT statement. ALTER VIEW is permitted only to the
definer or users with the SUPER privilege.

ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

DROP VIEW

DROP VIEW removes one or more views. You must have the DROP privilege for each view.

DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]

CREATE INDEX/DROP INDEX

The CREATE INDEX statement is used to create indexes in tables.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

The DROP INDEX statement is used to delete an index in a table.

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2/Oracle:

DROP INDEX index_name;

MySQL:

ALTER TABLE table_name
DROP INDEX index_name;