Database Domain 2 Lesson 1 to 2 Work With Tables Tables are created in a database using a CREATE statement. There should not be spaces in the names of fields, and columns should begin with a capital letter. To change a table, one can use an ALTER command. The ADD statement adds a column to a table. The DROP TABLE command removes a table from a database. Primary keys in a table should always have a value. NULL values can be in columns of a table. For columns that should not be null, add a NOT NULL condition to the column. Step for Completion: 1. What are three things that should be specified when creating a table? Answer: a. Table name b. Column names and their data types c. Constraints, such as primary keys or NOT NULL conditions 2. What value should be NOT NULL for every table? Answer: Primary key values should always be NOT NULL 3. How can a column’s value change from NULL to NOT NULL? Answer: To change a column's value from NULL to NOT NULL, you need to use an ALTER TABLE statement with the MODIFY or ALTER COLUMN clause and specify the NOT NULL condition for that column 4. If a table is no longer needed in a database, what command should one use to remove it? Answer: The DROP TABLE command is used to remove a table from a database. Create, Alter, and Drop Views A view is a saved query in a database. The process of creating and dropping views is the same as creating and dropping tables. The CREATE VIEW statement begins the view creation process. Using SELECT statements, one can choose what data to include in the view. The ALTER VIEW statement allows one to make changes to a view. To delete a view, one uses the DROP VIEW statement. Step for Completion: 1. Does a view store information? Explain! Answer: No, a view does not store information itself. It is a virtual table created by a query, and it displays data from the underlying tables. The view's definition is stored, but not the data. 2. What command is used to remove a view from a database? Answer: The DROP VIEW statement is used to remove a view from a database. 3. What is one advantage of using views to display data? Answer: One advantage of using views is that they provide a way to simplify complex queries. Views allow users to access specific columns or rows of a table, hiding the complexity of the underlying database structure. This can enhance security, as users may not need direct access to the tables but can work with the views instead. Procedures and Functions A stored procedure is a saved query that can accept input and output parameters. CREATE PROCEDURE statement begins a procedure. Input parameters are used as filters, and output parameters return values for apps to use. A RETURN function shows the results of a stored procedure. Some stored procedure statements do not allow other statements in the same window. Clustered and Nonclustered Indexes Indexing classifies queries using primary or compound keys. A clustered index uses the primary key of a table. It allows queries that utilize the primary key to run faster. Nonclustered indexes can be based on any other field. Using indexes adds size to a table, so they should be strategically built.