mysql Test1

  1. What software is needed to use a database.
    • client software
    • server software
    • SQL interface
  2. What components are needed to use a database?
    • user<->Database Application<->Database Management System(DBMS)<->Database
    • Image Upload 1
  3. DBMS
    Database Management System
  4. Desktop database systems typically:
    • 1) Have only a few tables
    • 2) Are simple in design
    • 3) Involve only a few computers
    • 4) Support only a few users
    • 5) MS-Access bundles the RDBMS and Application components together
  5. Name some organizational database systems.
    • IBM DB2
    • Oracle
  6. Organizational database systems typically:
    • 1) Support several users simultaneously
    • 2) Include more than one application
    • 3) Involve multiple computers
    • 4) Are complex in design
    • 5) Have many tables
    • 6) Have many databases
  7. picture Organizational Database System
    Image Upload 2
  8. SQL
    • Structured Query Language
    • It is the standard language used for retrieval and manipulation of data from a relational database.
  9. An SQL query...
    An SQL query or simply query is a programming statement. It is the method by which you retrieve and update information. Instead of pointing and clicking to add record, you issue an SQL Insert statement.
  10. MySQL Workbench is a(n)...
    editing environment.
  11. MySQL is...
    MySQL is a RDBMS that uses the SQL language. It is very popular for web applications. It is open source, scalable. It can handle 2 – 16 terabytes of data
  12. SQL DML statements
    • DML= Data Manipulation Language
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
  13. SQL DDL statements
    • DDL =Data Definition Language
  14. Programming Standards for Script Files
    • 1.Capitalize all keywords – SELECT, not select
    • 2.Use camel case for names -- hourlyPay, custID
    • 3.Start each clause on a new line
    • 4.Break long clauses into multiple lines
    • 5.Indent continued lines
    • 6.Place your name and a description of the script at the top of the file
    • 7.Further comments are only required for code that is difficult to understand or where requested
    • 8.Where possible, use part of table name in column name e.g. customer table column names: custID, custFName
  15. row and columns
    records and fields
  16. 1:M
    • one-to-many
    • primary key and foreign key
    • one vendor may have many invoices
    • PK in vendor table
    • FK in invoices table(same value for same vendor)
  17. EER
    Enhanced Entity Relationship (EER) Diagram.

    • A primary key is marked with a key icon.
    • A dark diamond shows a foreign key.
  18. The six basic steps for designing a data structure
    • 1:Identify the data elements
    • 2:Subdivide each element into its smallest useful components
    • 3:Identify the tables and assign columns
    • 4:Identify the primary and foreign keys
    • 5:Review whether the data structure is normalized
    • 6:Identify the indexes
  19. A persons name has components.
    The first name and last name are different components.
  20. 1NF
    First (1NF) The value stored at the intersection of each row and column must be a scalar value, and a table must not contain any repeating columns.
  21. The benefits of normalization
    • More tables, and each table has an index on its primary key. That makes data retrieval more efficient.
    • Each table contains information about a single entity. That makes data retrieval and insert, update, and delete operations more efficient.
    • Each table has fewer indexes, which makes insert, update, and delete operations more efficient.
    • Data redundancy is minimized, which simplifies maintenance and reduces storage
  22. 2NF
    Second (2NF)  Every non-key column must depend on the entire primary key.
  23. 3NF
    Third (3NF) Every non-key column must depend only on the primary key.
  24. A table is in first normal form (1NF) when:
    • A table is in first normal form (1NF) when:
    • 1.each column has a unique name
    • 2.the order of rows and columns doesn’t matter
    • 3.each column has a single data type
    • two rows contain identical values
    • 5.each column contains a single value
    • 6.columns do not contain repeating groups
  25. In Workbench, where can you see the databases and tables that are created?
    In the SCHEMAS in the Navigator(must be refreshed) on the left.
  26. What is another name for an entity?
  27. What is a schema?
    In mysql, another name for a database.
  28. Is MySQL a client or server application?
    server application
  29. In which form is a database considered to be normalized and ready for production?
  30. What are the requirements for a primary key?
    • 1) must be unique
    • 2) not subject to change
  31. How can you examine the tables?
    right click the table name and pick Select Row...
  32. How do you create the database from the script file?
    • 1) from file pick Open SQL Script.
    • 2) click lightning bolt execution icon.
  33. Is File, Run SQL Script the same as the lightning bolt icon?
  34. How do you view or change a table design?
    • Right click on table name and choose Alter Table.
    • Field names, datatypes, and other properties are listed below.
  35. How do you view a tables: field name, datatype and other properties?
    Right click on table name and choose Alter Table.
  36. common datatypes are
    • CHAR(n)
    • VARCHAR(n)
    • INT
    • DECIMAL(#digits,#decimal)
    • DATE    'yyyy-mm-dd'
    • BOOLEAN    0 or 1
  37. column attributes(constraints) are
    • NOT NULL
    • UNIQUE
    • DEFAULT value
  38. SQL Commenting
    • Block comment (more than one line)
    • /*  
    • stuff 
    • more stuff
    • */
    • Single line comment
    • -- This is a comment.  A space is required after the 2 hyphens.

    MySQL is NOT case sensitive. It ignores white space and every statement ends with a semicolon   ;
  39. How to create AP database?
    • -- create the database
  40. How do you select the AP database?
    • -- select the database
    • USE ap;
  41. Create the table on the one side of the relationship, use 2 types of constraints
    • CREATE TABLE province
    • (
    •    provID        CHAR(2)          PRIMARY KEY,
    •    provName   VARCHAR(25)  NOT NULL,
    •    provCap      VARCHAR(20)  NOT NULL,
    •    provJoined  DATE               NOT NULL,
    •    provPop     INT                  NOT NULL
    •  );
  42. Write the SQL code to create the table on the many side of the relationship, creating a join to the other table.
    • CREATE TABLE cave
    • (
    •    caveID                 INT    PRIMARY KEY,
    •    caveName            VARCHAR(25)
    •                                   NOT NULL   UNIQUE,
    •    caveLength         INT           DEFAULT null,
    •    cavePublicAccess BOOLEAN  DEFAULT null,
    •    provID                CHAR(2),
    •      CONSTRAINT province_fk_cave
    •           FOREIGN KEY(provID)
    •           REFERENCES province (provID)
    • );
  43. How do you insert into a table?
    • insert into species values
    • (1,'Capybara',9,'fruit'),
    • (2,'Snake',25,'mice'),
    • (3,'Ferret',10,'pellets'),
    • (4,'Elephant',50,'hay');
    • -----------------------------------------------
    • create table species
    • (
    •    spsID INT PRIMARY KEY,
    •    spsType varchar(10) NOT NULL UNIQUE,
    •    spsLifeSpanYr decimal(4,1),
    •    spsDiet varchar(15) DEFAULT 'hay'
    • );
  44. How do you create a simple query?
    • 1) from File, New Query Tab
    • 2) type SELECT * FROM tableName;(make sure the proper table is bold in the Navigator)
    • Or
    • SELECT *
    • FROM database.tableName
    • 3) Run
  45. How do you display all orders in the om database sorted by customer_id?
    • SELECT *
    • FROM om.orders
    • ORDER BY customer_id;
  46. Write a query to display movie title, rating and its release date only for movies with a rating of 8 or more. Sort by rating.
    • SELECT filmName, filmIMDBrate, filmRelease
    • FROM film
    • WHERE filmIMDBrate>=8
    • ORDER BY filmIMDBrate;
  47. Write a query to display blu ray titles released after 2010.
    • SELECT filmName, filmBR, filmRelease
    • FROM film
    • WHERE filmBR=1
    •     AND filmRelease>'2000-12-31';
  48. Display director names that start with the letter F.
    • SELECT *
    • FROM film
    • WHERE filmDirector LIKE 'F%';
  49. List all the fields from the animal table, only animals whose name end with the letter 'o'.
    • SELECT *
    • FROM animal
    • WHERE anName LIKE '%o';
  50. Write a query to add 1 point to IMDb rating, naming the column New Rating.
    • SELECT filmName, filmIMDBrate, filmIMDBrate+1 AS 'New Rating'
    • FROM film;
  51. List animal's name, date of birth and purchase price, only for animals whose purchase price was between $85 to $1000. Sort results by purchase price, highest to lowest.
    • SELECT anName, anDOB, anPurchPrice
    • FROM animal
    • WHERE anPurchPrice BETWEEN 85 and 1000
    • ORDER BY anPurchPrice desc;
  52. Display "Elephant, Diet: Hay" in one column name Summary.
    • SELECT CONCAT(atType,', Diet: ',atDiet) AS Summary
    • FROM animaltype;
  53. List animals name, weight and DOB, only for animals born before 2014.
    • SELECT anName, anWeightkg, anDOB
    • FROM animal
    • WHERE anDOB<'2014-01-01';
  54. List animal's name, temperament, and purchase price. Also display the purchase price with $50 added to it. Name this new column 'New purchase price'.
    • SELECT anName, anTemp, anPurchPrice,
    •       anPurchPrice+50 AS 'New purchase price'
    • FROM animal;
  55. List animal's name, temperament, and purchase price. Also display 80% of the purchase price. Name this new column 'Amount for tax receipt'. Round the result to the nearest dollar.
    • SELECT anName, anTemp, anPurchPrice,
    •     ROUND(anPurchPrice*0.8,0) AS
    •         'Amount for tax receipt'
    • FROM animal;
  56. Display the first two initials of the animal's name. Make a new column heading.
    • SELECT LEFT(anName,2) AS 'Short'
    • FROM animal;
  57. How do you concatenate last, first from employee table.
    • SELECT CONCAT(last,', ',first) AS Name
    • FROM employee
  58. Display all fields where anWeightkg>=1000 OR anPurchPrice>=500
    • SELECT *
    • FROM animal
    • WHERE anWeightkg>=1000 OR anPurchPrice>=500;
  59. Use IN to display all field where the species type is 1 or 2.
    • SELECT *
    • From animal
    • WHERE spsID IN (1,2);
  60. Call the last letter of the name last. Sort by last.
    • Select *, Right(anName,1) AS last
    • From animal
    • ORDER BY last;
  61. How to create a database?
  62. How to drop a database?
  63. How to use a database?
    USE dbname;
  64. When you identify a column as a PRIMARY KEY, what two attributes are automatically changed?
    The column is forced to be NOT NULL and UNIQUE. An INDEX is automatically created.
  65. Where is a column-level constraint defined?
    in a column definition
  66. Where is a table-level constraint defined?
    after the columns have been defined
  67. memberFName is in member, clubName is in club, member and club are joined by clubMember.
    Display the memberFName and  the clubName separated by ', '.
    • SELECT CONCAT(memberFName,', ',clubName) AS 'Summary info'FROM member m    
    • JOIN clubMember cm
    • ON m.memberID = cm.memberID
    • JOIN club c        
    • ON cm.clubID = c.clubID;
  68. memberFName and memberLName are in member. memberHomePlanet is in memberExtra .member is 1:1 with memberExtra with memberID as the pk for both.
    Display memberFName, memberLName and memberHomePlanet.
    • SELECT memberFName, memberLName, memberHomePlanet
    • FROM member m    
    • JOIN memberExtra e        
    • ON m.memberID = e.memberID
    • ORDER BY memberLName;
  69. club constains clubName and clubFee. new_fee is 30% more than clubFee. Display clubName, clubFee and new_fee.
    • SELECT clubName, clubFee, clubFee*1.3 AS new_fee
    • FROM club
    • ORDER BY new_fee;
  70. member contains memberFName and memberLName. Display first and last name for first names starting with M or R.
    • SELECT memberFName, memberLName
    • FROM member
    • WHERE memberFName LIKE 'M%' OR memberFName LIKE 'R%
    • 'ORDER BY memberFName;
  71. Display all the columns of club for clubFees from 500 to 1500. Display from highest to lowest.
    • SELECT *
    • FROM club
    • WHERE clubFee<=1500 AND clubFee>=500
    • ORDER BY clubFee DESC;
  72. Create a new table that will be in a 1:1 relationship with member table, this table will contain the member's home planet. memberID is the pk for member. Use column-level constraints.
    • CREATE TABLE memberExtra
    • (
    •     memberID    INT        PRIMARY KEY     REFERENCES member (memberID),    memberHomePlanet    VARCHAR(20)
    • );
  73. Create the M:N table joining club and member.
    clubID is the pk for club and memberID is the pk for member. Use table-level constraints.
    • CREATE TABLE clubMember
    • (
    • clubID            INT,
    • memberID        INT,
    • PRIMARY KEY (clubID,memberID),    CONSTRAINT club_fk_clubMember
    •     FOREIGN KEY (clubID)
    •     REFERENCES club (clubID),
    • CONSTRAINT member_fk_clubMember
    •     FOREIGN KEY (memberID)
    •     REFERENCES member (memberID)
    • );
Card Set
mysql Test1
mysql Test1