D427 - DATABASE MANAGEMENT APPLICATIONS WITH 100% CORRECT ANSWERS 2024.

Schema The definition of how data in a database will be organized. - Create - Read - Update - Delete The basis of interacting with databases can be defined using the CRUD acronym. What does CRUD stand for? Brainpower 0:02 / 0:15 DATE: YYYY-MM-DD DATETIME: YYYY-MM-DD HH:MM:SS What is the DATE datatype syntax? What is the DATETIME datatype syntax? It will have 3 numbers before the decimal and 2 after. What does DECIMAL(3,2) indicate? Stored Procedures a series of commands stored on the database. This allows the reuse of long or detailed queries instead of writing them for each use. It also provides a safe way to deal with sensitive data, especially with those unfamiliar with SQL syntax. Data definition language (DDL) involves instructing the DBMS software on what tables will be in the database, what attributes will be in the tables, which attributes will be indexed, and so forth. data manipulation languages (DMLs) refers to the four basic operations that can and must be performed on data stored in any DBMS (or in any other data storage arrangement, for that matter): data retrieval, data update, insertion of new records, and deletion of existing records. SELECT * FROM CUSTOMER WHERE CUSTNUMB BETWEEN 1 AND 2; What would the SELECT statement look like if you use a BETWEEN. SELECT * FROM CUSTOMER WHERE HQCITY IN ('Atlanta', 'Chicago', 'Washington'); What would the SELECT statement look like if you use a IN. SELECT * FROM CUSTOMER WHERE HQCITY LIKE 'W%'; What would the SELECT statement look like if you use a LIKE. A% - The "%" means that any string of characters can follow afterwards. The percent sign represents zero or more arbitrary regular characters A_ - The _ means that there will be exactly one letter following the A. The underscore represents a single arbitrary regular character. What are the two ways of using the LIKE command? The clause can include the term ASC at the end to make ascending explicit or it can include DESC for descending order. The default order for ORDER BY is ascending. How can you make it descending order? SELECT AVG/SUM/MIN/MAX/COUNT(Column_Name) FROM SALES WHERE Name = 'Matt'; Command to use AVG/SUM/MIN/MAX/COUNT aggregate functions? o One is that the tables to be joined must be listed in the FROM clause. o Two is that the join attributes in the tables being joined must be declared and matched to each other in the WHERE clause. There are two specifications to make in the SELECT statement to make a join work. SELECT SPNAME FROM SALESPERSON, CUSTOMER WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM Example of JOIN CAUSE. Dr. CODD In 1970, Dr. _________ of IBM published in Communications of the ACM a paper entitled "A Relational Model of Data for Large Shared Data Banks." This paper marked the beginning of the field of relational databases. CREATE TABLE DROP TABLE Commands to create and drop a table? - When you create the column definition (known as a column constraint). - When you use the CONSTRAINT keyword (known as a table constraint). The CREATE TABLE command lets you define constraints in two different places: NOT WHERE Location NOT IN ('WV', TN' 'CA'); WHERE Total NOT Between '1000', 2000'; WHERE Name NOT LIKE 'Ma%'; You can exclude rows with other types of conditions by using the ______ operator. USE USE MainDatabase The _____ command tells the database system which database you're going to be using for the upcoming commands. SHOW SHOW DATABASES - lists all databases in the database system instance. SHOW TABLES - lists all tables in the default database. SHOW COLUMNS FROM TableName - lists all columns in the TableName table of the default database. SHOW CREATE TABLE TableName - shows the CREATE TABLE statement for the TableName table of the default database. You can use the _______ statement to provide specified information about a database. ALTAR TABLE TableName Add ColumnName DataType ALTAR TABLE TableName DRIP ColumnName ALTAR TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType The ALTAR Table adds, deletes, or modifies columns. What is the syntax for each of these? LIMIT Select * FROM CITY LIMIT 100; This will limit it to the first 100 rows. Some tables may contain thousands or millions of rows, and selecting all rows can take a long time. MySQL has a ______clause that limits the number of rows returned by a SELECT statement. CREATE TABLE Employee ( ID INT, NAME VARCHAR(60), PRIMARY KEY (ID), ); This would make the PRIMARY KEY the ID column. To set the primary key, you would include it in the _______ __________ statement. The syntax is: CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID) ); This would make the ManagerID be a foreign key of the ID column on the employee table. To set the foreign key, you would include it in the _______ __________ statement. The syntax is: A signed number may be negative. An unsigned number cannot be negative. With integers, remember the difference between signed and unsigned numbers! RESTRICT CASCADE SET NULL SET DEFAULT On a FOREIGN KEY for example: CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID), ON DELETE CASCADE, ON UPDATE SET NULL ); What are the four referential integrity constraints that can be used? What is the syntax? CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, NAME VARCHAR(60), UNIQUE (Name) ); How do you set a column constraint to be unique? CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, NAME VARCHAR(60), PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID) CHECK NAME (Name = 'Matt'); ); How to you implement a CHECK constraint?