Transcript Chapter 12
CPS120: Introduction to Computer Science Information Systems: Database Management Nell Dale • John Lewis Session Goals • Describe the elements of a database management system • Describe the organization of a relational database • Establish relationships among elements in a database • Write basic SQL statements Database Management Systems • A database can simply be defined as a structured set of data • A database management system (DBMS) is a combination of software and data made up of: – Physical database—a collection of files that contain the data – Database engine—software that supports access to and modification of the database contents – Database schema—a specification of the logical structure of the data stored in the database Database Management Systems Figure 12.6 The elements of a database management system The Relational Model • In a relational DBMS, the data items and the relationships among them are organized into tables – A table is a collection of records – A record is a collection of related fields – Each field of a database table contains a single data value – Each record in a table contains the same fields A Database Table Figure 12.7 A database table, made up of records and fields A Second Table A database table containing customer data Relationships • We can use a table to represent a collection of relationships between objects A database table storing current movie rentals Structured Query Language • The Structured Query Language (SQL) is a comprehensive database language for managing relational databases What is SQL • SQL is an ANSI standard language for accessing databases SQL SQL SQL SQL SQL stands for Structured Query Language can execute queries against a database can retrieve data from a database can insert new records in a database can delete records from a database Some Definitions • Database Tables – Databases contain objects called Tables. – Records of data are stored in these tables. Tables are identified by names (like "Persons", "Orders", "Suppliers"). – Tables contain Columns and Rows with data. Rows contain records (like one record for each person). Columns contain data (like First Name, Last Name, Address, and City). The Select Statement • The SELECT statement selects columns of data from a database • The tabular result is stored in a result table (called the result set) SQL Queries • With SQL, we can Query a database and have a Result returned in a tabular form. • SELECT LastName FROM Persons Coding Select Statements • To select the columns named "LastName" and "FirstName", use a SELECT statement like this: – SELECT LastName,FirstName FROM Persons • To select all columns from the "Persons" table, use a * symbol instead of column name like this: – SELECT * FROM Persons The WHERE Clause • To conditionally select data from a table, a WHERE clause can be added to the SELECT statement with the following syntax: – SELECT column FROM table WHERE column condition value Where-clause Conditions • Relations that can be used: = <> > < >= <= BETWEEN LIKE Equal Not equal Greater than Less than Greater than or equal Less than or equal Between an inclusive range Wildcard Search The LIKE Condition • The LIKE condition is used to specify a search for a pattern in a column. • The syntax is like this: – SELECT column FROM table WHERE column LIKE pattern • A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern. Sample LIKE Statements • Select Persons with a Name Pattern • This SQL statement will return persons with a first name that start with an 'O'. – SELECT * FROM Persons WHERE FirstName LIKE 'O%' • This SQL statement will return persons with a first name that end with an 'a'. – SELECT * FROM Persons WHERE FirstName LIKE '%a' AND and OR • AND and OR join two or more conditions in a WHERE clause. – The AND operator displays a row if ALL conditions listed are true. – The OR operator displays a row if ANY of the conditions listed are true. • SELECT * FROM Persons WHERE FirstName='Paul'AND LastName='Millis' Between…And • The BETWEEN ... AND operator selects an inclusive range of data between two values. These values can be numbers, text, or dates. – SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 SQL Select Distinct • The DISTINCT keyword is used to return only distinct (different) values. – SELECT DISTINCT column-name(s) FROM table-name SQL Order By • The ORDER BY clause is used to sort the rows. • Example: To display the companies in alphabetical order: – SELECT Company, OrderNumber FROM OrdersORDER BY Company SQL Data Manipulation • SQL includes a syntax to update records with query and update commands SQL Data Definition • The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted, links between tables defined and, and constraints imposed between database tables.