Chapter 6 and 7: SQL Commands and Data Defintion

The SQL command used to filter rows based on a specified condition is __________.

WHERE

The __________ clause in SQL is used to specify groups of rows that have the same column value.

GROUP BY

To specify the order of the output in SQL, you use the __________ clause.

ORDER BY

The SQL term for removing duplicate rows from the result set is __________.

DISTINCT

To create a new database, the SQL command used is __________.

CREATE DATABASE database_name;

To identify multiple values in a WHERE clause, you use __________.

IN

The SQL command used to update existing data in a database is __________.

UPDATE table_name

SET column_name = new_value

WHERE condition;

The SQL command to delete a specific row from a table is __________.

DELETE FROM Viewing

WHERE propertyNo = 'PG4'

In SQL, __________ is used to remove all rows from a table without the possibility of a rollback.

DELETE FROM table_name

To add a new column in an existing table, you would use the __________ command.

ALTER TABLE table_name

ADD column_name datatype;

To remove a column from a table, you use __________ command.

ALTER TABLE table_name

DROP COLUMN column_name;

The __________ function can be used for both numeric and non-numeric data types.

COUNT, MIN, and MAX.

The SQL keyword that allows you to assign a name to a calculated field is __________.

AS.

To drop an entire table from the database, you would use the __________ command.

DROP TABLE table_name;;

To retrieve the total count of rows from a dataset, you use the __________ function.

COUNT.

How to create a table in SQL

CREATE TABLE Persons (
    column1 datatype,
column2 datatype,

….

PRIMARY KEY (column)
);

Syntax for adding a foreign key in SQL is __________.

ALTER TABLE table_name

ADD CONSTRAINT fk_name

FOREIGN KEY (column_name) REFERENCES other_table (other_column);

How to create a schema in SQL?

CREATE SCHEMA schema_name;

Syntax for Natural Join

SELECT *

FROM table1

NATURAL JOIN table2;

Syntax for Cross Join

SELECT *

FROM table1

CROSS JOIN table2;

Domain Constraints

Constrains the values of a single coumn

Entity Integrity

The primary key of a table must contain a unique, nonnull value of each row

Referential Integrity

If the foreign key contains a value, that value must refer to an existing, valid row in the parent table