Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
An overview of database management concepts, including schema, crud operations, data definition language (ddl), data manipulation language (dml), and various sql statements such as select, where, join, and like. It covers topics related to database design, including primary keys, foreign keys, referential integrity constraints, and indexing. The document also discusses sql commands for creating, altering, and deleting tables, as well as querying and manipulating data within a database. Additionally, it touches on the relational database model, sql syntax, and the components of a sql system. Overall, this document serves as a comprehensive guide to understanding the fundamentals of database management and sql programming.
Typology: Exams
1 / 29
This page cannot be seen from the preview
Don't miss anything!
Schema The definition of how data in a database will be organized.
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 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?
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),
How to you implement a CHECK constraint? CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, NAME VARCHAR(60), PRIMARY KEY (Code), CONSTRAINT DepartmentForeignKey FOREIGN KEY (ManagerID) REFERENCES Employee(ID) ); Table constraints may be named using the optional ________ keyword, followed by the constraint name and declaration. If no name is provided, the database generates a default name. INNER JOIN An ________ JOIN returns only those rows where the linking values match in both of the tables or in result sets. SELECT RecipeTitle, Preparation, RecipeClassDescription FROM Recipe_Classes INNER JOIN Recipes ON Recipe_Classes.RecipeClassID = Recipes.RecipeClassID INNER JOIN Example
An _______ JOIN asks your database system to return not only the rows that match on the criteria you specify (as the case with INNER JOIN) but also the unmatched rows from either one or both of the two sets you want to link. String For example, SELECT LOWER('MySQL'); would return mysql. ______ functions manipulate string values. HAVING SELECT CountryCode, SUM(Population) FROM City GROUP BY CountryCode HAVING SUM(Population) > 230000; The _________ clause is used with the GROUP BY clause to filter group results. AS SELECT EmployeeNames AS Names
A _____________________ compares columns with an operator other than =, such as < and >. self-join A _________________ joins a table to itself. A self-join can compare any columns of a table, as long as the columns have comparable data types. In this scenario, aliases are necessary to distinguish left and right tables. SELECT Model, Gigabytes FROM iPhone CROSS JOIN Storage A ______________ combines two tables without comparing columns.As a result, all possible combinations of rows from both tables appear in the result. The syntax is...... WITH CHECK OPTION CREATE VIEW view_name AS SELECT column1, column2, FROM table_name WHERE condition WITH CHECK OPTION;
To prevent inserts or updates that appear to fail, databases that support view updates have an optional ____________ __________ __________ clause. When this is specified the database rejects inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation. Entity A person is an entity. So is a building, a piece of inventory sitting on a shelf, a finished product ready for sale, and a sales meeting (an event). an object or event in our environment that we want to keep track of. Attribute Examples of attributes include an employee's employee number, the weight of an automobile, a company's address, or the date of a sales meeting. a property or characteristic of an entity. Cardinalities the maximum number of entities that can be involved in a particular relationship.
the percentage of table rows selected by a query. AD binary search o The database first compares the search value to an entry in the middle of the index. o If the search value is less than the entry value, the search value is in the first half of the index. If not, the search value is in the second half. o The database now compares the search value to the entry in the middle of the selected half, to narrow the search to one quarter of the index. o The database continues in this manner until it finds the index block containing the search value. the database repeatedly splits the index in two until it finds the entry containing the search value: primary index, also called a clustering index an index on a sort column secondary index, also called a nonclustering index an index that is not on the sort column. Dense Index
indexed-sequential In an _________ ___________ file, the file is stored on the disk in order based on a set of field values (in this case the salesperson numbers) and an index is built over that same field.
0 to 65, MEDIUMINT UNSIGNED 0 to 16,777, INT UNSIGNED 0 to 4,294,967, FLOAT -3.4E+38 to 3.4E+ SQL Founders American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) DDL Commands Create, Alter, Drop, Rename, Truncate,
