...dwqwdlqkdmqwkldnqwkldnwkdnwqdldkwd Solutions To Workshop Exercises Chapter 1: SQL and Data 4 Chapter 2: SQL: The Basics 13 Chapter 3: The WHERE and ORDER BY Clauses 20 Chapter 4: Character, Number, and Miscellaneous Functions 27 Chapter 5: Date and Conversion Functions 42 Chapter 6: Aggregate Functions, GROUP BY and HAVING 57 Chapter 7: Equijoins 73 Chapter 8: Subqueries 108 Chapter 9: Set Operators 126 Chapter 10: Complex Joins 137 Chapter 11: Insert, Update, and Delete 164 Chapter 12: Create, Alter, and Drop Tables 178 Chapter 13: Indexes, Sequences, and Views 188 Chapter 14: The Data Dictionary, Scripting, and Reporting 197 Chapter 15: Security 213 Chapter 16: Regular Expressions and Hierarchical Queries 222 Chapter 17: Exploring Data Warehousing Features 235 Chapter 18: SQL Optimization 248 Chapter 1: SQL and Data In this chapter, you learned about data, how data is organized in tables, and how the relationships among the tables are depicted in a schema diagram. Based on your newly acquired knowledge, design a schema diagram based on the fictional ACME Construction Company. Draw on your own work experience to design the following components. 1. Draw boxes for these three tables: EMPLOYEE, POSITION, and DEPARTMENT. Solution: See the solution for Exercise 3. 2. Create at least three columns for each of the tables and designate a primary key for each table. Solution: See the solution for Exercise 3. 3. Create relationships among the...
Words: 6586 - Pages: 27
...INSY 5337 Data Warehousing – Term Paper NoSQL Databases: An Introduction and Comparison between Dynamo, MongoDB and Cassandra Authored ByNitin Shewale Aditya Kashyap Akshay Vadnere Vivek Adithya Aditya Trilok Abstract Data volumes have been growing exponentially in recent years, this increase in data across all the business domains have played a significant part in the analysis and structuring of data. NoSQL databases are becoming popular as more organizations consider it as a feasible option because of its schema-less structure along with its capability of handling BIG Data. In this paper, we talk about various types of NoSQL databases based on implementation perspective like key store, columnar and document oriented. This research paper covers the consolidated applied interpretation of NoSQL system, depending on the various database features like security, concurrency control, partitioning, replication, Read/Write implementation. We also would draw out comparisons among the popular products and recommend a particular NoSQL solution on the above mentioned factors. 1. Introduction Until recently, Relational database systems have been on the forefront of data storage and management operations. The advent of mobile applications that requires real time analysis like GPS based services, banking and social media has led to huge unstructured data being produced every second. Traditional RDBMS systems have found it difficult to cater to these huge chunks of unstructured...
Words: 4246 - Pages: 17
...day in class prior to the final exam. Questions 1. What is a database? Describe the 3 main types discussed in the book. * Is a collection of related data. * Doesn’t have to be electronic; example : card catalog’s in libraries ; spiral notebook ; phone or address book * Usually mean electronic database that run on computers 2. Why is the relational database model most commonly used today? What impact does it have on data integrity? * The idea came from Edgar F Codd in 1970 he worked for IBM at the time * Advantage is the tables define the relationships among themselves by mean of repeating an attribute or column from one table in another table “called Keys” * One of the problems was data redundancy; means to store the same data in more than place in the database * Codd’s theoretical design minimized redundancy * The impact is; the complexity of the design, its easy to create a hard to use database, or hard to fine the data you entered into the database. So a well-designed database = data integrity and flexibility. 3. Distinguish between data integrity and redundancy. * Data integrity = to the accuracy and quality of the data * Redundancy = refers to data that is repeated in multiple places in a database. 4. What is an entity? * An object of concern to a database, such as a customer or sale. Used in the logical design phase of a database. The main key that starts the gathering of data into one place. ...
Words: 2656 - Pages: 11
...Create Table CREATE TABLE PRESENTERS (PRESENTERID CHAR (6) PRIMARY KEY NOT NULL, PNRLNAME VARCHAR (20) NOT NULL, PNRFNAME VARCHAR (20) NOT NULL, GENDER CHAR (2) DEFAULT ‘M’, AGE SMALLINT NOT NULL CHECK (AGE>=18), YEARS SMALLINT NOT NULL, SALARY_YEARLY DECIMAL (10, 2) NOT NULL); CREATE TABLE EPISODES (EPISODENO INT IDENTITY (1,1) PRIMARY KEY NOT NULL, EPISODENAME VARCHAR (50) NOT NULL, DATEAIRED DATE NOT NULL, GUEST VARCHAR (50), COUNTRYAIRED VARCHAR (10) NOT NULL, PRESENTERID CHAR (6) FOREIGN KEY REFERENCES PRESENTERS (PRESENTERID), CARNO CHAR (7) FOREIGN KEY REFERENCES CARS (CARNO)); Insert Into Table INSERT INTO PRESENTERS (PRESENTERID, PNRLNAME, PNRFNAME, AGE, YEARS, SALARY_YEARLY) VALUES ('EMP_01','NEEDELL','TIFF', 61, 10, 374500.70), ('EMP_02','BUTLER-HENDERSON','VICKI', 41, 10, 24262.55), ('EMP_03','PLATO','JASON', 45, 8, 29100.98); Alter Tables Add Column ALTER TABLE EMPLOYEES ADD LAST_NAME VARCHAR (50) Datatypes ALTER TABLE CAKES ALTER COLUMN CAKENO CHAR (8) NOT NULL Constraints ALTER TABLE EMPLOYEES ADD CONSTRAINT PK1 PRIMARY KEY (EMPID) Foreign Keys ALTER TABLE EPISODES ADD FOREIGN KEY (CAKENO) REFERENCES CAKES (CAKENO) Change Column SP_RENAME 'TABLE_NAME'.'OLD_COLUMN_NAME', 'NEW_COLUMN_NAME', 'COLUMN'; Drop Column ALTER TABLE EMPLOYEES DROP COLUMN LAST_NAME; Delete Row DELETE FROM CARS WHERE CARMAKE='SUBARU WRX' Drop Table DROP TABLE EMPLOYEES Update Tables UPDATE PRESENTERS SET SALARY_YEARLY...
Words: 1804 - Pages: 8
...Année universitaire Département Matière Enseignant Intitulé TD/TP : Durée 2012-2013 Informatique Bases de données avancées Haytham Elghazel TP JDBC/PLSQL 4h Année 4A Ce TP est à réaliser seul ou en binôme (trinômes interdits). Il est à rendre sur spiral (http://spiralconnect.univ-‐lyon1.fr/spiral/spiral.html#/activities/goto_folder/1940969) pour le 01/12/2012, 23h30. Il faut rendre le programme (projet maven + script SQL commenté dans un fichier zip). Ne pas oublier de mentionner les deux étudiants du binôme dans le nom du fichier zip qui sera sous la forme : TP2_BDAV_Nom1_Prenom1_Nom2_Prenom2.zip. Nom1_Prenom1 est le nom et le prénom du premier membre du binôme, Nom2_Prenom2 est le nom et le prénom du second membre du binôme. Le non-‐respect de ces consignes pourra être sanctionné dans la note de ce TP. On utilisera le schéma relationnel du forum de discussion mis en place au TP précédent auquel il faut rajouter la ligne suivante...
Words: 1924 - Pages: 8
...Stored procedures Stored procedures can help improve application performance and reduce database access traffic. All database access must go across the network, which, in some cases, can result in poor performance. For each SQL statement, a database manager application must initiate a separate communication with DB2. To improve application performance, you can create stored procedures that run on your database server. A client application can then simply call the stored procedures to obtain results of the SQL statements that are contained in the procedure. Because the stored procedure runs the SQL statement on the server for you, database performance is improved. In addition, stored procedures can help to centralize business logic. If you make changes to a stored procedure, the changes are immediately available to all client applications that use it. Stored procedures are programs that have the following characteristics: • Contain procedural constructs with SQL statements • Are stored in databases and run on DB2 servers • Can be called by name by an application that is using SQL • Allow an application program to run in two parts: the application on the client and the stored procedure on the server The following figures show how two client applications access a database located on a database server. A client application that does not use stored procedures to access a database can require more network traffic. A client application that takes advantage of a stored...
Words: 2097 - Pages: 9
...Exam Name: Exam Type: Exam Code: Oracle Database: SQL Fundamentals Oracle 1Z0-051 Total Questions 114 Question: 1 Which statement is true regarding the INTERSECT operator? A. It ignores NULL values. B. Reversing the order of the intersected tables alters the result. C. The names of columns in all SELECT statements must be identical. D. The number of columns and data types must be identical for all SELECT statements in the query. Answer: D Question: 2 Which three statements are true regarding the data types in Oracle Database 0g/11g? (Choose three.) A. Only one LONG column can be used per table. B. A TIMESTAMP data type column stores only time values with fractional seconds. C. The BLOB data type column is used to store binary data in an operating system file. D. The minimum column width that can be specified for a VARCHAR2 data type column is one. E. The value for a CHAR data type column is blank-padded to the maximum defined column width. Answer: A, D, E Question: 3 Examine the structure of the PROGRAMS table: Name Null? Type ---------- ------------- --------------PROG_ID NOT NULL NUMBER(3) PROG_COST NUMBER(8,2) START_DATE NOT NULL DATE END_DATE DATE Which two SQL statements would execute successfully? (Choose two.) A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs; B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs; C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs; D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date))...
Words: 11884 - Pages: 48
...query? 2.3 What does SQL stand for, and what is SQL? 2.4 What does SKU stand for? What is an SKU? 2.5 Summarize how data were altered and filtered in creating the Cape Codd data extraction. 2.6 Explain, in general terms, the relationships among the RETAIL_ORDER, ORDER_ITEM, and SKU_DATA tables. 2.7 Summarize the background of SQL. 2.8 What is SQL-92? How does it relate to SQL statements in this chapter? 2.9 What features have been added to SQL in versions subsequent to the SQL-92? 2.10 Why is SQL described as a data sublanguage? 2.11 What does DML stand for? What are DML statements? 2.12 What does DDL stand for? What are DDL statements? 2.13 What is the SQL SELECT /FROM/WHERE framework? 2.14 Explain how Access uses SQL. 2.15 Explain how enterprise-class DBMS products use SQL. 2.16 There is an intentional flaw in the design of the INVENTORY table used in these exercises. This flaw was purposely included in the INVENTORY tables so that you can answer some of the following questions using only that table. Compare the SKU and INVENTORY tables, and determine what design flaw is included in INVENTORY. Specifically, why did we include it? 2.17 Write an SQL statement to display SKU and SKU_Description. 2.18 Write an SQL statement to display SKU_Description and SKU. 2.19 Write an SQL statement to display Warehouse. 2.20 Write an SQL statement to display Warehouse...
Words: 7421 - Pages: 30
...Languages “SQL is a tool for organizing, managing, and retrieving data stored by a computer database.”¹ This tool has been around for many decades and has evolved over time much like any of today’s technology has. Below is a chart found in Chapter 3 of SQL: The Complete Reference, Third Edition showing the milestones in the development of SQL: Milestones in SQL Development | Year | Event | 1970 | Codd defines relational database model | 1974 | IBM begins System/R project | 1974 | First article describing the SEQUEL language is published | 1978 | System/R customer tests are conducted | 1979 | Oracle introduces first commercial RDBMS | 1981 | Relational Technology introduces Ingres | 1981 | IBM announces SQL/DS | 1982 | ANSI forms SQL standards committee | 1983 | IBM announces DB2 | 1986 | ANSI SQL1 standard is ratified | 1986 | Sybase introduces RDBMS for transaction processing | 1987 | ISO SQL1 standard is ratified | 1988 | Ashton-Tate and Microsoft announce SQL Server for OS/2 | 1989 | First TPC benchmark (TPC-A) is published | 1990 | TPC-B benchmark is published | 1991 | SQL Access Group database access specification is published | 1992 | Microsoft publishes ODBC specification | 1992 | ANSI SQL2 standard (SQL-92) is ratified | 1992 | TPC-C (OLTP) benchmark is published | 1993 | Specialized SQL data warehousing systems are shipped for the first time | 1993 | ODBC products are shipped for the first time | 1994 | Parallel database server...
Words: 1281 - Pages: 6
...Query Language (SQL) is a specialized language for updating, deleting, and requesting information from databases. SQL is an ANSI and ISO standard, and is the de facto database query language. A variety of established database products support SQL, including products from Oracle and Microsoft SQL Server. It is widely used in both industry and academia, often for enormous, complex datbases. (Base, 2013). SQL was developed in the 1970’s by IBM to initially manipulate and retrieve data in IBM system R. The SQL language was standardized in 1986 by the American National Standards Institute (ANSI); however, later releases were released as International Organization Standardization (ISO) standards. (Inc., 2013) Although IBM authored SQL, the first SQL implementation was provided by Oracle Corporation (then called Relational Software Inc.). Early commercial implementations were concentrated on midsized UNIX-based DBMSs, such as Oracle, Ingres, and Informix. IBM followed in 1981 with SQL/DS, the forerunner to DB2, which debuted in 1983. (Unknown, 2010) ANSI published the first SQL standard (SQL-86) in 1986. An international version of the standard issued by ISO appeared in 1987. A significant update to SQL-86 was released in 1989 (SQL-89). Virtually, all relational DBMSs that you encounter today support most of the 1986 standard. (Unknown, 2010) In 1992, the standard was revised again (SQL-92), adding more capabilities to the language. Because SQL-92 was s superset of SQL-89, older database...
Words: 802 - Pages: 4
...What is a reflective cross-site scripting attack? A reflective cross site scripting attack is when a single HTTP response is used to inject browser executable code. It is not actually placed in the application. 4. What common method of obfuscation is used in most real-world SQL attacks? They include character scrambling and masking, numeric variance and nulling, relying on an array of built-in SQL Server system functions used for string manipulation. 5. Which web application attack is more prone to extracting privacy data elements out of a database? SQL injections can be used to enter the database with administrator rights. The best way to prevent this is to use Java instead. 6. Given that Apache and Internet Information Services are the two most popular Web applications servers for Linux and WS Windows platforms, what would you do to identify known software vulnerabilities and exploits? A public domain by definition is far different than a systems PKI server. A public domain that stores certs is in a key escrow. 7. If you can monitor when SWL injections are performed on an SQL database, what would you recommend as a security countermeasure to monitor your production SQL databases? Of course. That’s a CYA and common sense thing. 8. What can you do to ensure that your organization incorporates penetration testing and web application testing as part of its implementation...
Words: 438 - Pages: 2
...INTRODUCTION SQL is divided into the following Data Definition Language (DDL) Data Manipulation Language (DML) Data Retrieval Language (DRL) Transaction Control Language (TCL) Data Control Language (DCL) DDL -- create, alter, drop, truncate, rename DML -- insert, update, delete DRL -- select TCL -- commit, rollback, savepoint DCL -- grant, revoke CREATE TABLE SYNTAX Create table (col1 datatype1, col2 datatype2 …coln datatypen); Ex: SQL> create table student (no number (2), name varchar (10), marks number (3)); INSERT This will be used to insert the records into table. We have two methods to insert. a) By value method By address method USING VALUE METHOD Syntax: insert into (table_name) values (value1, value2, value3 …. Valuen); © Copy rights are reserved. 2 Ex: SQL> insert into student values (1, ’sudha’, 100); SQL> insert into student values (2, ’saketh’, 200); To insert a new record again you have to type entire insert command, if there are lot of records this will be difficult. This will be avoided by using address method. b) USING ADDRESS METHOD Syntax: insert into (table_name) values (&col1, &col2, &col3 …. &coln); This will prompt you for the values but for every insert you have to use forward slash. Ex: SQL> insert into student values (&no, '&name', &marks); Enter value for no: 1 Enter value for name: Jagan Enter value for marks: 300 old new SQL> 1:...
Words: 42387 - Pages: 170
...Learning Team: Create Database, Table & Inserts Read and follow the instructions carefully and completely for submission of the Week 3 Learning Team Assignment. NOTE: This is a large assignment. Starting early in the week and giving your team time to post code for a review and assistance will help you prepare it accurately and completely. I encourage everyone to work on the assignment throughout the week and not wait until the day it is due to begin the work. Please see the Instructor Policies for Team Participation credit guidelines. REQUIRED: Coding Standards for this course are required. Please see the attached Coding Standards. CRITICAL Resource: The attached Errors Table is a resource for dealing with error messages and other potential obstacles you may encounter writing code. Optional: Read the Week 2 and 3 Handouts in the Recommended Learning Activities to assist with creating this assignment. Optional: Complete the Week 2 and 3 Learning Activity Tutorials in the Recommended Learning Activities. REQUIRED: Use the Kudler Chart of Accounts PROVIDED. Analyze the "PROVIDED" Chart of Accounts spreadsheet in order to decide how you will design the database. Plan your Data Dictionary (You will be submitting data dictionary with your final project submission, Week 5.) REQUIRED: Use the Data Dictionary Template attached to create the Data Dictionary for the Learning Team Assignment. REQUIRED: Follow the instructions in the provided...
Words: 1276 - Pages: 6
...SQL & QBE There are three typical operations that are done in querying databases (relational algebra): (1) Project—select columns [SELECT in SQL] (2) Restrict—select rows [WHERE] (3) Join—select columns and merge on rows that meet conditions [FROM] & [WHERE] Relational algebra is not used in current systems. It is a conceptual/theoretical way to manipulate RDBs. Structured Query Language (SQL) is a widely used language that retrieves and updates data in tables and views (manipulate RDBs). QBE is a user interface that simplifies SQL procedures. Other than some minor syntax differences, SQL is standardized. It is very powerful—i.e. you can do almost anything with data tables that you want. It is also simple to use. SQL is set based—returns a subset of tables referenced. Action queries enable user to change, insert, create, and delete data sets (tables). Selection queries retrieve and display data. Parameter queries prompt for input information. Dynasets are temporary tables that Access uses to store data resulting from a query. Tables must be related if used in a query. Natural join (equijoin or inner join)—most common kind of join. Two tables are joined on the common (join) column. The WHERE (=) statement specifies the join column(s) in which the rows have to match. Outer join (full)—all rows from both tables are included in output table (left and right outer joins would include all rows in one table but only the matches from the other). ...
Words: 1410 - Pages: 6
...the performance of how fast data is moved through the disks on the server. You don’t want the server to always use I/O instead they are going to want the data cashed because it doesn’t have to search the disks to retrieve the data. 4. Database statistics are measurements from the DBMS. They usually look at tables, columns and indexes that are being used. By measuring those that can see the performance of the system and make critical decisions to make the system better. 5. There are many different programs out there that can measure database statistics. Some commands that are built in the system to obtain the stats are to use to ANALYZE command. 6. Most measurement that are typical are the number of disk blocks being utilized, also the number of rows and how long the length of the rows are. Also including the smallest value within each column, including column that also are utilizing indexes. 7. They are two different command that do two different outputs. A DML statement manipulates the data. But a DDL updates the data directory and the system catalog. 8. Here are the 3 phases: * Parsing –this step parses the SQL query and uses the most efficient way to execute it. * Execution- this step executes a SQL query. * Fetching – this grabs the data and send the output back to the client requesting the data...
Words: 761 - Pages: 4
