MySQL Ch. 1

If a foreign key is defined in MySQL you have have it enforce what?

Referential integrity

What is an INNER JOIN

Rows from the two tables in the join are included in the result table only if their related columns match

Statement that changes the value of a credit_total column for a selected row in the Invoices table

UPDATE invoices SET credit_total = 35.89 WHERE invoice_number = '367447'

What statement to select current database (ap)

USE ap

What is a many-to-many relationship

intermediate table that has one-to-many relationship with 2 tables in the many-to-many

What happens when SELECT retrieves data from 2 tables

it is joined together into a single result

Even though SQL is a standard language each vendor has what?

its own SQL dialect or variant

What does the LIMIT clause do

limits the number of rows in the result set

What is referential integrity

means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

API's for PHP

mysqli (MySQL Improved Extension) and PDO (PHP Data Objects)

what follows the FROM clause

names the table that contains the columns called the base table

What does a row mean in a table

one instance of the entity

What do you do when defining a column

select the data that can stored and assign properties

When specifying a column what do you have to do

specify a value unless it allows null values or has a default value

what follows a SELECT statement

the names of columns to be retrieved

What does ALL and DISTINCT do?

they specify whether or not duplicate rows are returned

What is the * for in MySQL

to get all columns

How to code an column alias

use the AS keyword followed by the name ex. SELECT invoice_number AS "Invoice Number"

What are the statements that work with data in a database called

Data Manipulation Language (DML)

API's use what to communicate with a database

Database Driver

what is a function that returns a full name

SELECT CONCAT(first_name, ' ', last_name) AS full_name

Expanded syntax of the SELECT Clause

SELECT [ALL | DISTINCT] column_specification [[AS] result_column][, column_specificationss [[AS] result_column]]...

What is the syntax for SELECT statement

SELECT select_list [FROM table_source] [WHERE search_condition] [ORDER BY order_by_list] [LIMIT row_limit]

What does DELETE do

Removes an existing row

What is Connector/Net

Connects .Net applications to a MySQL database

What is Connector/J

Connects Java applications to a MySQL database

What does SELECT do

Retrieve data from one or more tables

When was MySQL released

2000

What is a one-to-one relationship

the data in the 2 tables could be stored in a single table

What is a join

'join' used to connect two or more tables logically with or without common field(s)

What is the Server

(Database Server) computer that has enough processor speed, RAM and disk storage to store the files and databases of the system and provide services to the clients

What operators are there in MySQL

+, - , *, %, /, DIV

how to single line comment in MySQL

--

How to block comment

/* */

What are statements that create databases and work with the objects within a database

Data Definition Language (DDL)

What statement adds a new column to a table

ALTER TABLE table_name ADD column_name DECIMAL (9,2)

what happened in SQL in 1999

ANSI published SQL3 which incorporated new features and support for objects. Levels of compliance were dropped and replaced by a core specification that defined the essential elements for compliance

what happened in SQL in 2003

ANSI published SQL4 (ANSI/ISO SQL:2003) these standards introduced XML-related features, standardized sequences, and identity columns

what happened in SQL in 2006

ANSI published SQL:2006 which defined how SQL can be used with XML

what happened in SQL in 2016

ANSI published SQL:2016, whihc includes support for regular expressions and JavaScript Object Notation (JSON)

what happened in SQL in 1989

ANSI published first set of standards for a database query language called ANSI/ISO SQL-89 or SQL1

what happened in SQL in 1992

ANSI published revised standards that were more stringent and incorporated many new features. new levels of compliance and conformancne

What does the WHERE clause do

Acts as filter in a SELECT statement. This clause specifies a condition that can be evaluated as either true or false. If met it selects certain rows

What does INSERT do

Adds new rows to a table

What does API stand for and what is it?

Application Programming Interface

The processing done by a DBMS is referred to as

Back-end processing

What are 2 common MySQL drivers

Connector/J Connector/Net

What statement to create a databae named ap

CREATE DATABASE ap

What statement to create tables

CREATE TABLE table_name ( define each column in the table including data type, whether or not it allows null values, and default value if it has one )

What is the network

Cabling, communication lines, network interface cards, hubs, routers, and other components

What does ALTER TABLE do

Changes the definition of an existing table

What does ALTER INDEX do

Changes the structure of an existing index

Whats one factor to consider when assigning data type to columns

Choose the type that minimizes the use of disk space because it improves performance

What are the components of a client/server

Client, network, Server

What does CREATE DATABASE do

Creates a new database on the server

What does CREATE INDEX do

Creates a new index for a table

What does CREATE TABLE do

Creates a new table in a database

What does DROP DATABASE do

Deletes an existing database and all of its tables

What does DROP INDEX do

Deletes an existing index

What does DROP TABLE do

Deletes an existing table

What does the ORDER BY clause do

Determines how the rows in the result set are sorted

what happened in SQL in 1970

Dr. E. F. Codd developed the relational database model

the processing done by client software is

Front-End

what happened in SQL in 1985

IBM released DB2

what happened in SQL in 1982

IBM released their first RDBMS called SQL/DS

What is the most common type of join

INNER JOIN

how to add rows to a table called invoices

INSERT INTO invoices (vendor_id, invoice_number, other row names,) VALUES (12, '3289175' , ...)

What is a unique key

In a database table, the Unique Key is a column which may or may not have null value of each of the row within that column.

How is data stored in a Relational database

In one or more tables

What are indexes

Index helps retrieve the data quicker, if implemented correctly.

What do rows contain in tables

Information about a single vendor

What are Relational Databases and what are they good for

It eliminated some of the problems that were associated with standard files and other database designs. It reduced data redundancy

What do you use to access a MySQL database from a Java application

JDBC (Java Database Connectivity)

what happened in SQL in 1987

Microsoft released SQL server

What does UPDATE do

Modifies existing rows

what happened in SQL in 1995

MySQL AB released MySQL for internal use

what happened in SQL in 2000

MySQL became an open-source database

When coding SELECT clauses can they be in any order?

No they must be in the order of SELECT, FROM, WHERE, ORDER BY, LIMIT

what happened in SQL in 1979

Relational software, Inc (named oracle) released first RDBMS

what happened in SQL in 2010

Oracle acquired Sun Microsystems and MySQL. Soon after the acquisition many of the original developers of MySQL left and begin working on a fork of the open-source code named MariaDB

What are 3 popular databases

Oracle, DB2, Microsoft SQL Server

Relationships in tables exist between

Primary key in one table and the foreign key in another table

What is a composite primary key

Primary key that consists of 2 or more columns

what happened in SQL in 2008

Sun MicroSystems acquired MySQL & ANSI published SQL:2008 which introduced INSTEAD OF triggers and the TRUNCATE statement

What is an enterprise system

System consists of networks, midrange systems, and mainframe systems

What is a primary key

Table contains one or more columns that uniquely identify each row in the table.

What are the clients

The PC's that are already available on the desktops through a company

What is a foreign key

This is a column in one table that points to the primary key in another table.

What is MySQL used for

To deliver cost-effective, high-performance, scalable database applications... the type that the web is built on

What does a cell in a table contain

Value

What clauses are optional in the SELECT statement

WHERE, ORDER BY, LIMIT

What are some companies that use MySQL

Wikipedia, Facebook, Twitter

What does it mean that SQL is a freeform language

You can include line breaks, spaces, and indentation without affecting the way the database interprets the code

What is a result set (result table)

a logical table thats created temporarily within a database

What is an auto increment column

a numeric column whose value is generated automatically when a row is added to the table

What is a default value

a value that is assigned to the column if another value isnt provided

What is a Null value in columns

a value thats unknown, unavailable, not applicable

What is an OUTER JOIN

all rows are retained, regardless of match (null values inserted into blanks)

What is an Entity-Relationship diagram or Enhanced-entity relationship

can be used to show how the tables in a database are defined and related

What is a one-to-many relationship

each row relating to one or more rows in another table