...DD1334 Databasteknik Laboration 1: SQL Basics Andreas Gustafsson, Hedvig Kjellström, Michael Minock and John Folkesson The purpose of Laboration 1 is to learn how to retrieve rmation stored in relational databases. You will learn 1) how to formulate SQL queries and understand how they apply to the schema and how they are executed; 2) how constraints effect insertions and deletions; 3) about the basics of view, transactions, indices and triggers. The recommended reading for Laboration 1 is that of Lectures 1-7 , particularly Chap 6-8. Laboration Come prepared to the review session! Only one try is allowed – if you fail, you are not allowed to try again until the next lab session. The review will take 10 minutes or less, so have all papers in order. To pass you should have Completed Task 1 with at least 9 of the 10 queries right, completed Task 2 and able to explain why certain actions give errors and other do not, also 8 of the 9 steps should be documented showing you executed them correctly, Task 3 the output file should show that the trigger works as it should. The grade is A if passed when the review when due. See the Lab Grading page in bilda contents for the due dates for the labs and the grading of late assignments. Laboration 1 is intended to take 30h to complete. Computing Environment In this assignment you will use Nestor 2.0. Nestor is KTH’s logic engine (computer) dedicated to hold the databases used in this and other similar courses. Nestor is aptly named after...
Words: 2303 - Pages: 10
...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
............. 7 7. ER Schema................................................................................. 8 8. Data Dictonary ...........................................................................9-16 8.1 Table Library................................................................9 8.2 Table Book....................................................................10 8.3 Table Course.................................................................11 8.4 Table Journal................................................................12 8.5 Table Compact Disc......................................................13 8.6 Table Employee............................................................14 8.7 Table Student ..............................................................15 8.8 Table department.........................................................16 9. sql statement to create Tables.....................................................17-28 9.1 sql statement to create library.....................................17 9.2 sql statement to create...
Words: 2401 - Pages: 10
...Teach Yourself SQL in 21 Days, Second Edition Table of Contents: Introduction Week 1 at a Glance Day 1 Introduction to SQL Day 2 Introduction to the Query: The SELECT Statement Day 3 Expressions, Conditions, and Operators Day 4 Functions: Molding the Data You Retrieve Day 5 Clauses in SQL Day 6 Joining Tables Day 7 Subqueries: The Embedded SELECT Statement Week 1 in Review Week 2 at a Glance Day 8 Manipulating Data Day 9 Creating and Maintaining Tables Day 10 Creating Views and Indexes Day 11 Controlling Transactions Day 12 Database Security Day 13 Advanced SQL Topics Day 14 Dynamic Uses of SQL Week 2 in Review Week 3 at a Glance Day 15 Streamlining SQL Statements for Improved Performance Day 16 Using Views to Retrieve Useful Information from the Data Dictionary Day 17 Using SQL to Generate SQL Statements Day 18 PL/SQL: An Introduction Day 19 Transact-SQL: An Introduction Day 20 SQL*Plus Day 21 Common SQL Mistakes/Errors and Resolutions Week 3 in Review Appendixes A Glossary of Common SQL Statements B Source Code Listings for the C++ Program Used on Day 14 C Source Code Listings for the Delphi Program Used on Day 14 D Resources E ASCII Table F Answers to Quizzes and Excercises © Copyright, Macmillan Computer Publishing. All rights reserved. Teach Yourself SQL in 21 Days, Second Edition Acknowledgments A special thanks to the following individuals: foremost to my loving wife, Tina, for her tolerance and endless support, to Dan Wilson for his...
Words: 128515 - Pages: 515
...you need to return a backup of your database in the form of sql scripts. Create a zip file packaging everything together and upload this .zip file on the Blackboard. The deadline is March 4th, 2015 Each day of late delivery will cost you a penalty of -25%. You are expected to use an SQL DBMS software (preferably MySQL server) to design your database and then transfer your code in a word/latex document. This document has to be editable. Do not upload pdf files because it is not easy for us to copy-paste your contents while grading. Please also deliver, within your document, the output of the queries. For each one of the tasks there is included very detailed information about the delivery requirements, which you have to read very carefully. START OF PROJECT DESCRIPTION “Database for the management of your books” You are provided with a description of the following procedure: Brief description You want to develop a database in order to organize your books. Looking at your bookshelf you notice that you have some books more than one time: there might be a book you have multiple times but each copy has been published by different publishers. Your books are of various thematic categories (history, novel, poetry, science). Each one of your books is either a hard-copy or a soft-copy. The condition of your books varies from poor to mint (poor, acceptable, good, very good, mint). Every now and then, you buy new books and you want to somehow keep track of your purchase activity...
Words: 1498 - Pages: 6
...C H A P T E R 3 SQL Chapter 3 covers the relational language SQL. The discussion is based on SQL:1999. Integrity constraint and authorization features of SQL being a large language, many of its features are not covered here, and are not appropriate for an introductory course on databases. Standard books on SQL, such as Date and Darwen [1993] and Melton and Simon [1993], or the system manuals of the database system you use can be used as supplements for students who want to delve deeper into the intricacies of SQL. Although it is possible to cover this chapter using only handwritten exercises, we strongly recommend providing access to an actual database system that supports SQL. A style of exercise we have used is to create a moderately large database and give students a list of queries in English to write and run using SQL. We publish the actual answers (that is the result relations they should get, not the SQL they must enter). By using a moderately large database, the probability that a “wrong” SQL query will just happen to return the “right” result relation can be made very small. This approach allows students to check their own answers for correctness immediately rather than wait for grading and thereby it speeds up the learning process. A few such example databases are available on the Web home page of this book. Exercises that pertain to database design are best deferred until after Chapter 7. Exercises 3.8 Consider the insurance database of Figure 3.11, where...
Words: 762 - Pages: 4
...Database Management System Basith Shaik Southern New Hampshire University September 7, 2015 Abstract The intent of this paper is to design and propose a database management system solution to Grandfield College for tracking software installed. I have analyzed the organizational issues and needs and developed conceptual, logical, and physical designs of DBMS solution. In order to implement the solution, substantial research had been done on best practices in design, available products, and the legal and ethical standards to which we must adhere during design. This paper includes Business rules, Conceptual, Logical, and Physical database designs, Recommendations on best DBMS required for Grandfield College, Data model, Legal Compliance, Ethical Practices, Security Needs and Security Plan Keywords: Grandfield College, Database Design, Recommendations, Business Rules, Data model, Security Plan Database Management System Grandfield College is in need of Database Management System as law requires that any business, including a school, track its software. It is important to know what software the school owns, in what versions, and what the license agreement for that software is. For this purpose I have developed a Software Tracking database for Grandfield College. Problem Grandfield College is in need of a database for effectively tracking faculty and staff computers, the software installed on those systems, User access to each computer, and requests for new software installation...
Words: 4672 - Pages: 19
...® OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051) ABOUT THE AUTHORS John Watson (Oxford, UK) works for BPLC Management Consultants, teaching and consulting throughout Europe and Africa. He was with Oracle University for several years in South Africa, and before that worked for a number of companies, government departments, and NGOs in England and Europe. He is OCP qualified in both database and Application Server administration. John is the author of several books and numerous articles on technology and has 25 years of experience in IT. Roopesh Ramklass (South Africa), OCP, is an independent Oracle specialist with over 10 years of experience in a wide variety of IT environments. These include software design and development, systems analysis, courseware development, and lecturing. He has worked for Oracle Support and taught at Oracle University in South Africa for several years. Roopesh is experienced in managing and executing IT development projects, including infrastructure systems provisioning, software development, and systems integration. About the Technical Editor Bruce Swart (South Africa) works for 2Cana Solutions and has over 14 years of experience in IT. Whilst maintaining a keen interest for teaching others, he has performed several roles including developer, analyst, team leader, administrator, project manager, consultant, and lecturer. He is OCP qualified in both database and developer roles. He has taught at Oracle University...
Words: 150089 - Pages: 601
...SQL Cursor A SQL cursor is a private Oracle SQL working area. There are two types of SQL cursor: implicit or explicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers. Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL. For example, • SQL%ROWCOUNT, return the number of rows affected; • SQL%FOUND, a BOOLEAN attribute indicating whether the recent SQL statement matches to any row; • SQL%NOTFOUND, a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row; • SQL%ISOPEN, a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed. To write the explicit cursor, please refer to the following example. Note that a cursor definition can array a number of arguments. For example, DECLARE CURSOR csr_ac (p_name VARCHAR2) IS SELECT empno, name, sal FROM employee WHERE name LIKE '%p_name%'; BEGIN FOR rec_ac IN csr_ac ('LE') LOOP DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal); END LOOP ; CLOSE csr_ac; END; / Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following. SQL> DECLARE 2 CURSOR csr_ac (p_name VARCHAR2) IS 3 SELECT empno, ename, sal 4 FROM emp 5 6 WHERE ename LIKE '%SMITH%'; 7 8 v_a emp.empno%TYPE; ...
Words: 2868 - Pages: 12
...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
...Teradata Database Release Summary Release 12.0 B035-1098-067A March 2008 The product or products described in this book are licensed products of Teradata Corporation or its affiliates. Teradata, BYNET, DBC/1012, DecisionCast, DecisionFlow, DecisionPoint, Eye logo design, InfoWise, Meta Warehouse, MyCommerce, SeeChain, SeeCommerce, SeeRisk, Teradata Decision Experts, Teradata Source Experts, WebAnalyst, and You’ve Never Seen Your Business Like This Before are trademarks or registered trademarks of Teradata Corporation or its affiliates. Adaptec and SCSISelect are trademarks or registered trademarks of Adaptec, Inc. AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc. BakBone and NetVault are trademarks or registered trademarks of BakBone Software, Inc. EMC, PowerPath, SRDF, and Symmetrix are registered trademarks of EMC Corporation. GoldenGate is a trademark of GoldenGate Software, Inc. Hewlett-Packard and HP are registered trademarks of Hewlett-Packard Company. Intel, Pentium, and XEON are registered trademarks of Intel Corporation. IBM, CICS, RACF, Tivoli, z/OS, and z/VM are registered trademarks of International Business Machines Corporation. Linux is a registered trademark of Linus Torvalds. LSI and Engenio are registered trademarks of LSI Corporation. Microsoft, Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and other countries. Novell and SUSE are registered trademarks...
Words: 18345 - Pages: 74
...You are to enter all answers or Prtscrn requirements into this Word Document. You are not permitted to submit any other document format, e.g., Wordpad, PDFs, etc. that is not based on this original Word document. This document contains hidden internal markers and applications that will track the version of this assignment and your assignment progress. You MUST submit the assignments using the Word document(s) provided you. You may not use any other word processor, except Microsoft Word. Do not use Open Office DOCX files. When an instructor has possession of an electronic document it is very easy to detect plagiarism. Many instructors use Turnitin assignments, which is applicable to assignments that permit cut-and-paste as this assignment. It is very easy to compare multiple copies of word documents (see link below). Microsoft provides a variety of FREE anti-plagiarizing tools. And there is a wide variety of tools that can analyze hidden information within a Word document (see sample link below). Changing fonts, margins and spacing does not do it anymore. Even when individuals try to artificially change content, a Word document contains hidden markers that may provide an audit trail to find previous authors and computer systems who have edited the document. Comparing and merging Microsoft Word documents - http://support.microsoft.com/kb/306484 Compare documents side by side - http://office.microsoft.com/en-us/word-help/compare-documents-side-by-side-HA010102251...
Words: 6416 - Pages: 26
...reality in a way that supports processes requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database management systems (DBMSs) are computer software applications that interact with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases. Well-known DBMSs include MySQL, PostgreSQL,Microsoft SQL Server, Oracle, SAP and IBM DB2. A database is not generally portable across different DBMSs, but different DBMSs can interoperate by using standards such asSQL and ODBC or JDBC to allow a single application to work with more than one DBMS. Database management systems are often classified according to the database modelthat they support; the most popular database systems since the 1980s have all supported the relational model as represented by the SQL language. Sometims a DBMS is loosely referred to as a "database". database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images. In computing, databases are sometimes classified according to their organizational approach. The most prevalent approach is therelational database, a tabular database in which data is defined so that it can be reorganized...
Words: 4833 - Pages: 20
...also available as a concatenated page, suitable for printing or saving as a PDF for offline viewing. MET CS669 Database Design and Implementation for Business This course uses the latest database tools and techniques for persistent data and object-modeling and management. Students gain extensive hands-on experience with exercises and a term project using Oracle, SQL Server, and other leading database management systems. Students learn to model persistent data using the standard Entity-Relationship model (ERM) and how to diagram those models using EntityRelationship Diagrams (ERDs), Extended Entity-Relationship Diagrams (EERDs), and UML diagrams. Students learn the standards-based Structured Query Language (SQL) and the extensions to the SQL standards implemented in Oracle and SQL Server. Students learn the basics of database programming, and write simple stored procedures and triggers. The Role of this Course in the MSCIS Online Curriculum This is a core course in the MSCIS online curriculum. It provides students with an understanding and experience with database technology, database design, SQL, and the roles of databases in enterprises. This course is a prerequisite for the three additional database courses in the MSCIS online curriculum, which are CS674 Database Security, CS699 Data Mining and Business Intelligence and CS779 Advanced Database Management. By taking these three courses you can obtain the Concentration in Database Management and Business Intelligence...
Words: 10777 - Pages: 44
...Introduction to Oracle9i: SQL Instructor Guide • Volume 1 40049GC11 Production 1.1 October 2001 D33993 Authors Nancy Greenberg Priya Nathan Copyright © Oracle Corporation, 2000, 2001. All rights reserved. This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this document...
Words: 33172 - Pages: 133
