Colorado Technical UniversityInstructor: Dr. John ConklinUnit 3 – DDL & DMLCS 660 – Database SystemsColorado Technical University2Database HumorSource: (https://slideplayer.com/slide/14170766/)
SQL – Data ManipulationObjectives• Purpose and importance of SQL.• How to retrieve data from database using SELECT and:• Use compound WHERE conditions.• Sort query results using ORDER BY.• Use aggregate functions.• Group data using GROUP BY and HAVING.• Use subqueries.• Join tables together.• Perform set operations (UNION).• How to update database using INSERT, UPDATE, and DELETE.4Objectives of SQL• Ideally, database language should allow user to:• create the database and relation structures;• perform insertion, modification, deletion of data fromrelations;• perform simple and complex queries.• Must perform these tasks with minimal user effortand command structure/syntax must be easy tolearn.• It must be portable.5Objectives of SQL (cont.)• SQL is a transform-oriented language with 2 majorcomponents:• A DDL for defining database structure.• A DML for retrieving and updating data.• Until SQL:1999, SQL did not contain flow of controlcommands. These had to be implemented using aprogramming or job-control language, or interactivelyby the decisions of user.6• SQL is relatively easy to learn:• it is non-procedural – you specify what information yourequire, rather than how to get it;• it is essentially free-format.7Objectives of SQL (cont.)Consists of standard English words:1) CREATE TABLE Staff(staffNo VARCHAR(5),lName VARCHAR(15),salary DECIMAL(7,2));2) INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);3) SELECT staffNo, lName, salaryFROM StaffWHERE salary > 10000; 8Objectives of SQL (cont.)• Can be used by range of users including DBAs,management, application developers, and other typesof end users.• An ISO standard now exists for SQL, making it boththe formal and de facto standard language forrelational databases.9Objectives of SQL (cont.)History of SQL• In 1974, D. Chamberlin (IBM San Jose Laboratory)defined language called ‘Structured English QueryLanguage’ (SEQUEL).• A revised version, SEQUEL/2, was defined in 1976 butname was subsequently changed to SQL for legalreasons.10History of SQL (cont.)• Still pronounced ‘see-quel’, though official pronunciation is‘S-Q-L’.• IBM subsequently produced a prototype DBMS calledSystem R, based on SEQUEL/2.• Roots of SQL, however, are in SQUARE (SpecifyingQueries as Relational Expressions), which predates SystemR project.11History of SQL (cont.)• In late 70s, ORACLE appeared and was probably first commercial RDBMSbased on SQL.• In 1987, ANSI and ISO published an initial standard for SQL.• In 1989, ISO published an addendum that defined an ‘IntegrityEnhancement Feature’.• In 1992, first major revision to ISO standard occurred, referred to as SQL2 orSQL/92.• In 1999, SQL:1999 was released with support for object-oriented datamanagement.• In late 2003, SQL:2003 was released.• In summer 2008, SQL:2008 was released.• In late 2011, SQL:2011 was released.12Importance of SQL• SQL has become part of application architectures suchas IBM’s Systems Application Architecture.• It is strategic choice of many large and influentialorganizations (e.g. X/OPEN).• SQL is Federal Information Processing Standard (FIPS)to which conformance is required for all sales ofdatabases to American Government.13Importance of SQL (cont.)• SQL is used in other standards and even influencesdevelopment of other standards as a definitional tool.Examples include:• ISO’s Information Resource Directory System (IRDS)Standard• Remote Data Access (RDA) Standard.14Writing SQL Commands• SQL statement consists of reserved words and userdefined words.– Reserved words are a fixed part of SQL and must be speltexactly as required and cannot be split across lines.– User-defined words are made up by user and representnames of various database objects such as relations,columns, views.15Writing SQL Commands (cont.)• Most components of an SQL statement are caseinsensitive, except for literal character data.• More readable with indentation and lineation:• Each clause should begin on a new line.• Start of a clause should line up with start of other clauses.• If clause has several parts, should each appear on a separateline and be indented under start of clause.16Writing SQL Commands (cont.)• Use extended form of BNF (Backus-Naur Form ) notation:– Upper-case letters represent reserved words.– Lower-case letters represent user-defined words.– | indicates a choice among alternatives.– Curly braces indicate a required element.– Square brackets indicate an optional element.– … indicates optional repetition (0 or more).17Source: BNF Notation: http://www.cs.nuim.ie/~jpower/Courses/Previous/parsing/node23.htmlLiterals• Literals are constants used in SQL statements.• All non-numeric literals must be enclosed in single quotes(e.g. ‘London’).• All numeric literals must not be enclosed in quotes (e.g.650.00).18SELECT StatementSELECT [DISTINCT | ALL]{* | [columnExpression [AS newName]] [,…] }
FROM[WHERE
TableName [alias] [, …]condition]
[GROUP BY[ORDER BY
columnList] [HAVING condition]columnList]
19SELECT Statement (cont.)SELECT Specifies which columns are to appear inoutput.FROM Specifies table(s) to be used.WHERE Filters rows.GROUP BY Forms groups of rows with same columnvalue.HAVING Filters groups subject to some condition.ORDER BY Specifies the order of the output.20SELECT Statement (cont.)• Order of the clauses cannot be changed.• Only SELECT and FROM are mandatory.21All Columns, All Rows• List full details of all staff.SELECT staffNo, fName, lName, address, position, sex, DOB, salary, branchNoFROM Staff;• Can use * as an abbreviation for ‘all columns’:SELECT *FROM Staff;22All Columns, All Rows (cont.)23Specific Columns, All RowsProduce a list of salaries for all staff, showing only staffnumber, first and last names, and salary.SELECT staffNo, fName, lName, salaryFROM Staff;24Specific Columns, All Rows (cont.)25Use of DISTINCTList the property numbers of allproperties that have been viewed.SELECT propertyNoFROM Viewing;26• Use DISTINCT to eliminateduplicates:SELECT DISTINCTpropertyNoFROM Viewing;27Use of DISTINCT (cont.)Calculated FieldsProduce list of monthly salaries for all staff, showingstaff number, first/last name, and salary.SELECT staffNo, fName, lName, salary/12 FROM Staff;28Calculated Fields (cont.)• To name column, use AS clause:SELECT staffNo, fName, lName, salary/12 AS monthlySalaryFROM Staff;29Comparison Search ConditionList all staff with a salary greater than 10,000.SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary > 10000;30Compound Comparison Search ConditionList addresses of all branch offices in London or Glasgow.SELECT *FROM BranchWHERE city = ‘London’ OR city = ‘Glasgow’;31Range Search ConditionList all staff with a salary between 20,000 and 30,000.SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary BETWEEN 20000 AND 30000;• BETWEEN test includes the endpoints of range.32Range Search Condition (cont.)• Also a negated version NOT BETWEEN.• BETWEEN does not add much to SQL’s expressivepower. Could also write:SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary >=20000 AND salary <= 30000;• Useful, though, for a range of values.33Set MembershipList all managers and supervisors.SELECT staffNo, fName, lName, positionFROM StaffWHERE position IN (‘Manager’, ‘Supervisor’);34Set Membership (cont.)• There is a negated version (NOT IN).• IN does not add much to SQL’s expressive power. Could haveexpressed this as:SELECT staffNo, fName, lName, positionFROM StaffWHERE position=‘Manager’ OR position=‘Supervisor’;• IN is more efficient when set contains many values.35Pattern MatchingFind all owners with the string ‘Glasgow’ in their address.SELECT ownerNo, fName, lName, address, telNoFROM PrivateOwnerWHERE address LIKE ‘%Glasgow%’;36Pattern Matching (cont.)• SQL has two special pattern matching symbols:• %: sequence of zero or more characters;• _ (underscore): any single character.• LIKE ‘%Glasgow%’ means a sequence of characters ofany length containing ‘Glasgow’.37NULL Search ConditionList details of all viewings on property PG4 where a comment hasnot been supplied.• There are 2 viewings for property PG4, one with and one withouta comment.• Have to test for null explicitly using special keyword IS NULL:SELECT clientNo, viewDateFROM ViewingWHERE propertyNo = ‘PG4’ AND comment IS NULL;38NULL Search Condition (cont.)• Negated version (IS NOT NULL) can test fornon-null values.39Single Column OrderingList salaries for all staff, arranged in descending order ofsalary.SELECT staffNo, fName, lName, salaryFROM StaffORDER BY salary DESC;40Multiple Column OrderingProduce abbreviated list of properties in order of propertytype.SELECT propertyNo, type, rooms, rentFROM PropertyForRentORDER BY type;41Multiple Column Ordering (cont.)• Four flats in the previous list – as no minor sort key specified,system arranges these rows in any order it chooses.• To arrange in order of rent, specify minor order:SELECT propertyNo, type, rooms, rentFROM PropertyForRentORDER BY type, rent DESC;42SELECT Statement – Aggregates• ISO standard defines five aggregate functions:• COUNT returns number of values in specifiedcolumn.• SUMreturns sum of values in specified column.• AVG returns average of values in specified column.• MINreturns smallest value in specified column.• MAX returns largest value in specified column.43SELECT Statement – Aggregates (cont.)• Each operates on a single column of a table andreturns a single value.• COUNT, MIN, and MAX apply to numeric andnon-numeric fields, but SUM and AVG may beused on numeric fields only.• Apart from COUNT(*), each functioneliminates nulls first and operates only onremaining non-null values.44SELECT Statement – Aggregates (cont.)• COUNT(*) counts all rows of a table,regardless of whether nulls or duplicatevalues occur.• Can use DISTINCT before column name toeliminate duplicates.• DISTINCT has no effect with MIN/MAX,but may have with SUM/AVG.45SELECT Statement – Aggregates (cont.)• Aggregate functions can be used only in SELECTlist and in HAVING clause.• If SELECT list includes an aggregate functionand there is no GROUP BY clause, SELECT listcannot reference a column out with an aggregatefunction. For example, the following is illegal:SELECT staffNo, COUNT(salary)FROM Staff;46Use of COUNT(*)How many properties cost more than £350 permonth to rent?SELECT COUNT(*) AS myCountFROM PropertyForRentWHERE rent > 350;47Use of COUNT(DISTINCT)How many different properties viewed in May ‘13?SELECT COUNT(DISTINCT propertyNo) AS myCountFROM ViewingWHERE viewDate BETWEEN ‘1-May-13’ AND ‘31-May-13’;48Use of COUNT and SUMFind number of Managers and sum of their salaries.SELECT COUNT(staffNo) AS myCount,SUM(salary) AS mySumFROM StaffWHERE position = ‘Manager’;49Use of MIN, MAX, AVGFind minimum, maximum, and average staff salary.SELECT MIN(salary) AS myMin,MAX(salary) AS myMax,AVG(salary) AS myAvgFROM Staff;50SELECT Statement – Grouping• Use GROUP BY clause to get sub-totals.• SELECT and GROUP BY closely integrated: each item inSELECT list must be single-valued per group, and SELECTclause may only contain:• column names• aggregate functions• constants• expression involving combinations of the above.51SELECT Statement – Grouping (cont.)• All column names in SELECT list must appear inGROUP BY clause unless name is used only in anaggregate function.• If WHERE is used with GROUP BY, WHERE isapplied first, then groups are formed from remainingrows satisfying predicate.• ISO considers two nulls to be equal for purposes ofGROUP BY.52Use of GROUP BYFind number of staff in each branch and their total salaries.SELECT branchNo,COUNT(staffNo) AS myCount,SUM(salary) AS mySumFROM StaffGROUP BY branchNoORDER BY branchNo;53Restricted Groupings – HAVING clause• HAVING clause is designed for use with GROUPBY to restrict groups that appear in final resulttable.• Similar to WHERE, but WHERE filters individualrows whereas HAVING filters groups.• Column names in HAVING clause must alsoappear in the GROUP BY list or be containedwithin an aggregate function.54Use of HAVINGFor each branch with more than 1 member of staff,find number of staff in each branch and sum oftheir salaries.SELECT branchNo,COUNT(staffNo) AS myCount,SUM(salary) AS mySumFROM StaffGROUP BY branchNoHAVING COUNT(staffNo) > 1ORDER BY branchNo;55Subqueries• Some SQL statements can have a SELECTembedded within them.• A sub select can be used in WHERE andHAVING clauses of an outer SELECT, where it iscalled a subquery or nested query.• Sub selects may also appear in INSERT,UPDATE, and DELETE statements.56Subquery with EqualityList staff who work in branch at ‘163 Main St’.SELECT staffNo, fName, lName, positionFROM StaffWHERE branchNo =(SELECT branchNoFROM BranchWHERE street = ‘163 Main St’);57Subquery with Equality (cont.)• Inner SELECT finds branch number for branch at ‘163Main St’ (‘B003’).• Outer SELECT then retrieves details of all staff whowork at this branch.• Outer SELECT then becomes:SELECT staffNo, fName, lName, positionFROM StaffWHERE branchNo = ‘B003’;58Subquery with AggregateList all staff whose salary is greater than theaverage salary, and show by how much.SELECT staffNo, fName, lName, position,salary – (SELECT AVG(salary) FROM Staff) As SalDiffFROM StaffWHERE salary >(SELECT AVG(salary)FROM Staff);59Subquery with Aggregate (cont.)• Cannot write ‘WHERE salary > AVG(salary)’• Instead, use subquery to find average salary (17000),and then use outer SELECT to find those staff withsalary greater than this:SELECT staffNo, fName, lName, position,salary – 17000 As salDiffFROM StaffWHERE salary > 17000;60Subquery Rules• ORDER BY clause may not be used in a subquery(although it may be used in outermost SELECT).• Subquery SELECT list must consist of a single columnname or expression, except for subqueries that useEXISTS.• By default, column names refer to table name in FROMclause of subquery. Can refer to a table in FROM using analias.61Subquery Rules (cont.)• When subquery is an operand in a comparison,subquery must appear on right-hand side.• A subquery may not be used as an operand in anexpression.62Nested subquery: use of INList properties handled by staff at ‘163 Main St’.SELECT propertyNo, street, city, postcode, type, rooms, rentFROM PropertyForRentWHERE staffNo IN(SELECT staffNoFROM StaffWHERE branchNo =(SELECT branchNoFROM BranchWHERE street = ‘163 Main St’));63Multi-Table Queries• Can use subqueries provided result columns comefrom same table.• If result columns come from more than one table mustuse a join.• To perform join, include more than one table in FROMclause.• Use comma as separator and typically includeWHERE clause to specify join column(s).64Multi-Table Queries (cont.)• Also possible to use an alias for a table named inFROM clause.• Alias is separated from table name with a space.• Alias can be used to qualify column names whenthere is ambiguity.65Simple JoinList names of all clients who have viewed a propertyalong with any comment supplied.SELECT c.clientNo, fName, lName,propertyNo, commentFROM Client c, Viewing vWHERE c.clientNo = v.clientNo;66Simple Join (cont.)• Only those rows from both tables that have identical valuesin the clientNo columns (c.clientNo = v.clientNo) areincluded in result.• Equivalent to equi-join in relational algebra.67Alternative JOIN Constructs• SQL provides alternative ways to specify joins:FROM Client c JOIN Viewing v ON c.clientNo = v.clientNoFROM Client JOIN Viewing USING clientNoFROM Client NATURAL JOIN Viewing• In each case, FROM replaces original FROM andWHERE. However, first produces table with twoidentical clientNo columns.68Sorting a joinFor each branch, list numbers and names of staff who manageproperties, and properties they manage.SELECT s.branchNo, s.staffNo, fName, lName, propertyNoFROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoORDER BY s.branchNo, s.staffNo, propertyNo;69Three Table JoinFor each branch, list staff who manageproperties, including city in which branch islocated and properties they manage.SELECT b.branchNo, b.city, s.staffNo, fName, lName, propertyNoFROM Branch b, Staff s, PropertyForRent pWHERE b.branchNo = s.branchNoAND s.staffNo = p.staffNoORDER BY b.branchNo, s.staffNo, propertyNo;70Multiple Grouping ColumnsFind number of properties handled by each staffmember.SELECT s.branchNo, s.staffNo, COUNT(*) AS myCountFROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoGROUP BY s.branchNo, s.staffNoORDER BY s.branchNo, s.staffNo;71Computing a JoinProcedure for generating results of a join are:1. Form Cartesian** product of the tables named in FROM clause.2. If there is a WHERE clause, apply the search condition to eachrow of the product table, retaining those rows that satisfy thecondition.3. For each remaining row, determine value of each item in SELECTlist to produce a single row in result table.72**The Cartesian product, also referred to as a cross-join, returns all the rows in all the tables listed in thequery. Each row in the first table is paired with all the rows in the second table. This happens when there is norelationship defined between the two tables.Source: (http://www.dba-oracle.com/t_garmany_9_sql_cross_join.htm)Computing a Join (cont.)4. If DISTINCT has been specified, eliminate any duplicate rowsfrom the result table.5. If there is an ORDER BY clause, sort result table as required.• SQL provides special format of SELECT for Cartesian product:SELECT [DISTINCT | ALL] {* | columnList}FROM Table1 CROSS JOIN Table273Outer Joins• If one row of a joined table is unmatched, row isomitted from result table.• Outer join operations retain rows that do not satisfy thejoin condition.• Consider following tables:74Outer Joins (cont.)• The (inner) join of these two tables:SELECT b.*, p.*FROM Branch1 b, PropertyForRent1 pWHERE b.bCity = p.pCity;75Outer Joins (cont.)• Result table has two rows where cities are same.• There are no rows corresponding to branches inBristol and Aberdeen.• To include unmatched rows in result table, use anOuter join.76Left Outer JoinList branches and properties that are in same city alongwith any unmatched branches.SELECT b.*, p.*FROM Branch1 bLEFT JOIN PropertyForRenta1 p ON b.bCity = p.pCity;• Includes those rows of first (left) table unmatched with rows from second (right)table.• Columns from second table are filled with NULLs.77Right Outer JoinList branches and properties in same city and any unmatched properties.SELECT b.*, p.*FROM Branch1 bRIGHT JOIN PropertyForRenta1 p ON b.bCity = p.pCity;• Right Outer join includes those rows of second (right) table that areunmatched with rows from first (left) table.• Columns from first table are filled with NULLs.78Full Outer JoinList branches and properties in same city and any unmatched branches orproperties.SELECT b.*, p.*FROM Branch1 bFULL JOIN PropertyForRenta1 p ON b.bCity = p.pCity;• Includes rows that are unmatched in both tables.• Unmatched columns are filled with NULLs.79EXISTS and NOT EXISTS• EXISTS and NOT EXISTS are for use only with subqueries.• Produce a simple true/false result.• True if and only if there exists at least one row in result table returnedby subquery.• False if subquery returns an empty result table.• NOT EXISTS is the opposite of EXISTS.80EXISTS and NOT EXISTS (cont.)• As (NOT) EXISTS check only for existence or non-existenceof rows in subquery result table, subquery can contain anynumber of columns.• Common for subqueries following (NOT) EXISTS to be ofform:(SELECT * …)81Query using EXISTSFind all staff who work in a London branch.SELECT staffNo, fName, lName, positionFROM Staff sWHERE EXISTS (SELECT * FROM Branch bWHERE s.branchNo = b.branchNo AND city = ‘London’);82Query using EXISTS (cont.)• Note, search condition s.branchNo = b.branchNo isnecessary to consider correct branch record for eachmember of staff.• If omitted, would get all staff records listed out becausesubquery:SELECT * FROM Branch WHERE city=‘London’• would always be true and query would be:SELECT staffNo, fName, lName, position FROM StaffWHERE true;83Query using EXISTS (cont.)• Could also write this query using join construct:SELECT staffNo, fName, lName, positionFROM Staff s, Branch bWHERE s.branchNo = b.branchNo ANDcity = ‘London’;84Union, Intersect, and Difference (Except)• Can use normal set operations of Union, Intersection, andDifference to combine results of two or more queries into asingle result table.• Union of two tables, A and B, is table containing all rows ineither A or B or both.• Intersection is table containing all rows common to both Aand B.• Difference is table containing all rows in A but not in B.• Two tables must be union compatible.85Union, Intersect, and Difference (Except) (cont.)• Format of set operator clause in each case is:op [ALL] [CORRESPONDING [BY {column1 [, …]}]]• If CORRESPONDING BY specified, set operation performedon the named column(s).• If CORRESPONDING specified but not BY clause, operationperformed on common columns.• If ALL specified, result can include duplicate rows.86Union, Intersect, and Difference (Except) (cont.)87Use of UNIONList all cities where there is either a branch office or aproperty.SELECT cityFROM BranchWHERE city IS NOT NULLUNIONSELECT cityFROM PropertyForRentWHERE city IS NOT NULL;88Use of UNION (cont.)• OrSELECT *FROM BranchWHERE city IS NOT NULLUNION CORRESPONDING BY citySELECT *FROM PropertyForRentWHERE city IS NOT NULL;• Produces result tables from both queries and merges both tables together.89INSERTINSERT INTO TableName [ (columnList) ]VALUES (dataValueList)• columnList is optional; if omitted, SQL assumes a list of allcolumns in their original CREATE TABLE order.• Any columns omitted must have been declared as NULLwhen table was created, unless DEFAULT was specifiedwhen creating column.90INSERT (cont.)• dataValueList must match columnList as follows:• number of items in each list must be same;• must be direct correspondence in position of items in twolists;• data type of each item in dataValueList must becompatible with data type of corresponding column.91INSERT … VALUESInsert a new row into Staff table supplying data for allcolumns.INSERT INTO StaffVALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Assistant’, ‘M’, Date‘1957-05-25’, 8300, ‘B003’);92INSERT using DefaultsInsert a new row into Staff table supplying data forall mandatory columns.INSERT INTO Staff (staffNo, fName, lName, position, salary, branchNo)VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, 8100, ‘B003’);• OrINSERT INTO StaffVALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, NULL, NULL, 8100, ‘B003’);93INSERT … SELECT• Second form of INSERT allows multiple rows to becopied from one or more tables to another:INSERT INTO TableName [ (columnList) ]SELECT …94INSERT … SELECTAssume there is a table StaffPropCount that containsnames of staff and number of properties they manage:StaffPropCount(staffNo, fName, lName, propCnt)Populate StaffPropCount using Staff andPropertyForRent tables.95INSERT … SELECTINSERT INTO StaffPropCount(SELECT s.staffNo, fName, lName, COUNT(*)FROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoGROUP BY s.staffNo, fName, lName)UNION(SELECT staffNo, fName, lName, 0FROM StaffWHERE staffNo NOT IN(SELECT DISTINCT staffNoFROM PropertyForRent));96StaffPropCount(staffNo, fName, lName, propCnt)INSERT … SELECT• If second part of UNION is omitted, excludes thosestaff who currently do not manage any properties.97UPDATEUPDATE TableNameSET columnName1 = dataValue1[, columnName2 = dataValue2…][WHERE searchCondition]• TableName can be name of a base table or an updatableview.• SET clause specifies names of one or more columnsthat are to be updated.98UPDATE (cont.)• WHERE clause is optional:• if omitted, named columns are updated for all rows in table;• if specified, only those rows that satisfy searchCondition areupdated.• New dataValue(s) must be compatible with data typefor corresponding column.99UPDATE All RowsGive all staff a 3% pay increase.UPDATE StaffSET salary = salary*1.03;Give all Managers a 5% pay increase.UPDATE StaffSET salary = salary*1.05WHERE position = ‘Manager’;100UPDATE Multiple ColumnsPromote David Ford (staffNo=‘SG14’) to Manager andchange his salary to $18,000.UPDATE StaffSET position = ‘Manager’, salary = 18000WHERE staffNo = ‘SG14’;101DELETEDELETE FROM TableName[WHERE searchCondition]• TableName can be name of a base table or anupdatable view.• searchCondition is optional; if omitted, all rows aredeleted from table. This does not delete table. Ifsearch_condition is specified, only those rows thatsatisfy condition are deleted.102DELETE Specific RowsDelete all viewings that relate to property PG4.DELETE FROM ViewingWHERE propertyNo = ‘PG4’;Delete all records from the Viewing table.DELETE FROM Viewing;103
• SQL – Data Definition
104
Data Definition• SQL DDL allows database objects such as schemas, domains, tables,views, and indexes to be created and destroyed.• Main SQL DDL statements are:
CREATE SCHEMA
DROP SCHEMA
CREATE/ALTER DOMAIN
DROP DOMAIN
CREATE/ALTER TABLE
DROP TABLE
CREATE VIEW
DROP VIEW
• Many DBMSs also provide:CREATE INDEX DROP INDEX105Data Definition• Relations and other database objects exist in anenvironment.• Each environment contains one or more catalogs, andeach catalog consists of set of schemas.• Schema is named collection of related database objects.• Objects in a schema can be tables, views, domains,assertions, collations, translations, and character sets. Allhave same owner.106CREATE SCHEMACREATE SCHEMA [Name |AUTHORIZATION CreatorId ]DROP SCHEMA Name [RESTRICT | CASCADE ]• With RESTRICT (default), schema must be empty oroperation fails.• With CASCADE, operation cascades to drop all objectsassociated with schema in order defined above. If anyof these operations fail, DROP SCHEMA fails.107CREATE TABLECREATE TABLE TableName{(colName dataType [NOT NULL] [UNIQUE][DEFAULT defaultOption][CHECK searchCondition] [,…]}[PRIMARY KEY (listOfColumns),]{[UNIQUE (listOfColumns),] […,]}{[FOREIGN KEY (listOfFKColumns)REFERENCES ParentTableName [(listOfCKColumns)],[ON UPDATE referentialAction][ON DELETE referentialAction ]] [,…]}{[CHECK (searchCondition)] [,…] })108CREATE TABLE (cont.)• Creates a table with one or more columns of the specifieddataType.• With NOT NULL, system rejects any attempt to insert anull in the column.• Can specify a DEFAULT value for the column.• Primary keys should always be specified as NOT NULL.• FOREIGN KEY clause specifies FK along with thereferential action.109Example – CREATE TABLECREATE TABLE tblCustomers(CustID char(10) NOT NULL PRIMARY KEY,CustFName varchar(25) NOT NULL,CustLname varchar(50) NOT NULL,AddressID int NOT NULL,CountryID int NOT NULL,RegionID int NOT NULL);110–tblCustomersALTER TABLE [tblCustomers]ADD CONSTRAINT FK_AddressIDFOREIGN KEY (AddressID) REFERENCES tblCustAddress(AddressID);ALTER TABLE• Add a new column to a table.• Drop a column from a table.• Add a new table constraint.• Drop a table constraint.• Set a default for a column.• Drop a default for a column.111Example – ALTER TABLEChange Staff table by removing default of ‘Assistant’ forposition column and setting default for sex column tofemale (‘F’).ALTER TABLE StaffALTER position DROP DEFAULT;ALTER TABLE StaffALTER sex SET DEFAULT ‘F’;112–tblCustomersALTER TABLE [tblCustomers]ADD CONSTRAINT FK_AddressIDFOREIGN KEY (AddressID) REFERENCES tblCustAddress(AddressID);Example – ALTER TABLE (cont.)Remove constraint from PropertyForRent that staffare not allowed to handle more than 100 propertiesat a time. Add new column to Client table.ALTER TABLE PropertyForRentDROP CONSTRAINT StaffNotHandlingTooMuch;ALTER TABLE ClientADD prefNoRooms PRooms;113DROP TABLEDROP TABLE TableName [RESTRICT | CASCADE]e.g. DROP TABLE PropertyForRent;• Removes named table and all rows within it.• With RESTRICT, if any other objects depend for theirexistence on continued existence of this table, SQL doesnot allow request.• With CASCADE, SQL drops all dependent objects (andobjects dependent on these objects).114Assignment Description• The case study retail store has provided a list of reports and data manipulation tasks that are needed in the processing of orders for theircustomers. Answer the following:• What structured query language (SQL) statement scripts are needed to create the database schema for the relational database system andmanipulate the data in the solution that you are proposing to the company?• How does each of these scripts specifically support the goals and objectives of the company?• Examples are provided here to help with this assignment.• As indicated in Unit 1, it is assumed that Microsoft SQL Server Express Edition (with management tools) was chosen as the database platform. Itcan be downloaded for free at this Web site.• The project deliverables are as follows:• Data Manipulation Tasks• Insert 20 records into each table for testing purposes.• Delete an entire order by using the unique identifier for that order.• Update the price of a product by using the unique identifier for that product.• Add a minimum of 3 of your own data manipulation language (DML) scripts based on the needs and specifications of your retail store.• Report List• Total revenue (sales) per month, grouped by customer• Total revenue (sales) per month, grouped by product• Total count of products, grouped by category• Add minimum of 3 of your own report scripts based on the needs and specifications of your retail store (one must be a CROSSTAB)115Assignment Description (cont.)• SQL (4–5 pages)• Include the database definition language (DDL) scripts to CREATE to database schema as described in the entity–relationship (E–R)diagram (Unit 2).• Include the database manipulation scripts (DML) that will be used to INSERT, DELETE, and UPDATE data in the proposeddatabase system.• Include the SELECT, CROSSTAB, and AGGREGATE FUNCTION statements that will be used to read data from the proposeddatabase system.• Provide your analysis as to how this part of the project fulfills the mission and 1 or more goals of the case studyorganization.• Provide the following attachments (in addition to embedding in document):• DDL.sql (including CREATE and INSERT statements so that they execute in the correct order [top-down])• DML.sql (including DELETE and UPDATE statements so that they can be executed in any order as selected)• REPORT.sql (including SELECT, CROSSTAB, AGGREGATE FUNCTION statements so that they can be executed inany order as selected)• Note: You will embed each script in the Word document and also provide it as an attachment.• All sources should be cited both in-text and in References using APA format.• Name the document “yourname_CS660_IP3.doc.”116Contact Information• My e-mail address- [email protected]• Office Hours – Wednesday 6:00 P.M. – 7:00 P.M. CSTSaturday 11:00 A.M. – 12:00 P.M. CST• Live Chats – Wednesday 7:00 P.M. – 8:00 P.M. CST* Please note that only one live chat session per week is required forthis course. However, optional live chat sessions may be heldsporadically throughout the course.117Questions / Comments118(Ideas/Think Web Graphics, 2019).ReferencesColorado Technical University. (2019). Instructor’s guide for CS 251-1901A-01. Retrievedfrom Colorado Technical University Online, Virtual Campus, Course Overview:https://campus.ctuonline.eduConnolly, T. and C. Begg (2015). Database systems; a practical approach to design,implementation, and management, 6th ed. Portland, Pearson Education.Ideas/Think Web Graphics. (2019). In Desktop Publishing. Retrieved from:http://desktoppub.about.com/od/freeclipart/l/blidea1.htmhttps://www.pearson.com/us/higher-education/subject-catalog/download-instructorresources.html?key=23976240871323439795242019119
Get Professional Assignment Help Cheaply
Are you busy and do not have time to handle your assignment? Are you scared that your paper will not make the grade? Do you have responsibilities that may hinder you from turning in your assignment on time? Are you tired and can barely handle your assignment? Are your grades inconsistent?
Whichever your reason may is, it is valid! You can get professional academic help from our service at affordable rates. We have a team of professional academic writers who can handle all your assignments.
Our essay writers are graduates with diplomas, bachelor’s, masters, Ph.D., and doctorate degrees in various subjects. The minimum requirement to be an essay writer with our essay writing service is to have a college diploma. When assigning your order, we match the paper subject with the area of specialization of the writer.
Why Choose Our Academic Writing Service?
Plagiarism free papers
Timely delivery
Any deadline
Skilled, Experienced Native English Writers
Subject-relevant academic writer
Adherence to paper instructions
Ability to tackle bulk assignments
Reasonable prices
24/7 Customer Support
Get superb grades consistently
How It Works
1. Place an order
You fill all the paper instructions in the order form. Make sure you include all the helpful materials so that our academic writers can deliver the perfect paper. It will also help to eliminate unnecessary revisions.
2. Pay for the order
Proceed to pay for the paper so that it can be assigned to one of our expert academic writers. The paper subject is matched with the writer’s area of specialization.
3. Track the progress
You communicate with the writer and know about the progress of the paper. The client can ask the writer for drafts of the paper. The client can upload extra material and include additional instructions from the lecturer. Receive a paper.
4. Download the paper
The paper is sent to your email and uploaded to your personal account. You also get a plagiarism report attached to your paper.
Get Professional Assignment Help Cheaply
Are you busy and do not have time to handle your assignment? Are you scared that your paper will not make the grade? Do you have responsibilities that may hinder you from turning in your assignment on time? Are you tired and can barely handle your assignment? Are your grades inconsistent?
Whichever your reason may is, it is valid! You can get professional academic help from our service at affordable rates. We have a team of professional academic writers who can handle all your assignments.
Our essay writers are graduates with diplomas, bachelor’s, masters, Ph.D., and doctorate degrees in various subjects. The minimum requirement to be an essay writer with our essay writing service is to have a college diploma. When assigning your order, we match the paper subject with the area of specialization of the writer.
Why Choose Our Academic Writing Service?
Plagiarism free papers
Timely delivery
Any deadline
Skilled, Experienced Native English Writers
Subject-relevant academic writer
Adherence to paper instructions
Ability to tackle bulk assignments
Reasonable prices
24/7 Customer Support
Get superb grades consistently
How It Works
1. Place an order
You fill all the paper instructions in the order form. Make sure you include all the helpful materials so that our academic writers can deliver the perfect paper. It will also help to eliminate unnecessary revisions.
2. Pay for the order
Proceed to pay for the paper so that it can be assigned to one of our expert academic writers. The paper subject is matched with the writer’s area of specialization.
3. Track the progress
You communicate with the writer and know about the progress of the paper. The client can ask the writer for drafts of the paper. The client can upload extra material and include additional instructions from the lecturer. Receive a paper.
4. Download the paper
The paper is sent to your email and uploaded to your personal account. You also get a plagiarism report attached to your paper.
PLACE THIS ORDER OR A SIMILAR ORDER WITH Essay fount TODAY AND GET AN AMAZING DISCOUNT
The post Discussion on Database Systems appeared first on Essay fount.
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but you proved you are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 11***| Rating: ⭐⭐⭐⭐⭐
"This company is the best there is. They saved me so many times, I cannot even keep count. Now I recommend it to all my friends, and none of them have complained about it. The writers here are excellent."
"Order a custom Paper on Similar Assignment at essayfount.com! No Plagiarism! Enjoy 20% Discount!"
