WGU D426/D427 Syntax QUESTIONS AND CORRECT DETAILED ANSWERS (VERIFIED ANSWERS) ALREADY GRADED A+

List all Databases

SHOW DATABASES;

List all Tables in a database

USE Database;

SHOW TABLES;

List all columns and their properties in a table

SHOW COLUMNS

FROM TableName;

Create a Table

CREATE TABLE TableName (

ColumnName1 DataType,

ColumnName2 DataType,

ColumnName3 DataType,

...

);

Add a column to a table

ALTER TABLE TableName

ADD ColumnName DataType;

Modify a column in a table

ALTER TABLE TableName

CHANGE CurrentColumnName NewColumnName DateType;

Remove a column from a table

ALTER TABLE TableName

DROP ColumnName;

Aggregate function (single value output)

SELECT Function(Column) AS NewColumnName

FROM TableName;

Aggregate function (multi-value (summary) output)

SELECT LabelsColumn, FUNCTION(DataColumn)

FROM TableName

GROUP BY LabelsColumn;

Insert new data into a table (all columns)

INSERT INTO TableName

VALUES (Value1, Vaule2, Value3....);

Insert new data into a table (not all columns)

INSERT INTO TableName (Column1, Column2, Column3....)

VALUES (Value1, Value2, Value3....);

Insert multiple rows of data (not all columns)

INSERT INTO TableName (Column1, Column2, Column3....)

VALUES (Value1, Value2, Value3....),

(Value1, Value2, Value3....),

(Value1, Value2, Value3....);

Modify data in a table (specific rows)

UPDATE TableName

SET ColumnName = NewValue

WHERE Condition;

Modify data in a table (all rows)

UPDATE TableName

SET ColumnName = NewValue;

Remove data from a table

DELETE FROM TableName

WHERE Condition;

Remove all data from a table

DELETE FROM TableName;

Set a primary key on an existing table

ALTER TABLE TableName

ADD PRIMARY KEY (ColumnName);

Set a foreign key on an existing table

ALTER TABLE TableName

ADD FORIEGN KEY (Column) REFERENCE Table (Column);

Create an index on a table

CREATE INDEX IndexName ON TableName (ColumnName);

Output all values from a table

SELECT * FROM TableName;

Create a table with constraint functions

CREATE TABLE TableName (

ColumnName1 DataType Constraint1 Constraint2,

ColumnName2 DataType Constraint1

);

Create a table with complex constraints on numerical or date columns

CREATE TABLE TableName (

ColumnName1 DataType CHECK (ColumnName Condition),

ColumnName2 DataType CHECK (ColumnName Condition)

);

Create a table with complex constraints on string columns

CREATE TABLE TableName (

ColumnName1 DataType,

ColumnName2 DataType,

CHECK (ColumnName1 Constraint),

CHECK (ColumnName2 Constraint)

);

Create a view

CREATE VIEW ViewName AS

SELECT Column1, Column2, Column3...

FROM TableName;

Remove a view

DROP VIEW ViewName;

Sort by a column

SELECT ColumnName

FROM TableName

ORDER BY ColumnName;

Apply a hierarchal sort using multiple columns

SELECT Column1, Column2

FROM TableName

ORDER BY Column1, Column2;

Return data from 2 tables

SELECT Table1.ColumnName, Table2.ColumnName

FROM Table1

JOIN Table2

ON Table1.ColumnName = Table2.ColumnName;

Return specific records from a table

SELECT Column1, Column2

FROM TableName

WHERE Condition;

Set a primary key when creating a table

CREATE TABLE TableName (

ColumnName1 DataType,

ColumnName2 DataType,

ColumnName3 DataType,

...

PRIMARY KEY (Column)

);

Set a foreign key when creating a table

CREATE TABLE TableName (

ColumnName1 DataType,

ColumnName2 DataType,

ColumnName3 DataType,

...

FOREIGN KEY (Column) REFERENCES Table (Column)

);

Return data from 3 or more tables

SELECT Table1.Column, Table2.Column, Table3.Column

FROM Table1

JOIN Table2

ON Table1.Column = Table2.Column

JOIN Table3

ON Table1.Column = Table3.Column;

Select output data based on aggregate results

SELECT LabelColumn, FUNCTION(DataColumn)

FROM TableName

GROUP BY LabelColumn

HAVING Condition;

Use a sub-query

SELECT Column1, Column2, Column3...

FROM TableName

WHERE Column OPERATOR

(Select FUNCTION(Column)

FROM TableName)

;