Basic Database Notes (Sql)

In: Computers and Technology

Submitted By ChristinePrice
Words 1804
Pages 8
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 = SALARY_YEARLY+1000000
WHERE PNRLNAME='CLARKSON' RULES CREATE RULE CREATE RULE SALARY RULE
AS @SALARY >0;
DROP RULE
DROP RULE SALARYRULE
DROP DEFUALT
DROP DEFAULT GENDER
DROP CONSTRAINT
ALTER TABLE EMPLOYEES
DROP CONSTRAINT EMPLOYEES_PK;

ALTER TABLE EMPLOYEES
DROP PRIMARY KEY;

ALTER TABLE EMPLOYEES
ALTER COLUMN SALARY DROP DEFAULT;

Queries
SELECT *…...

Similar Documents

Database - Sql

...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

Assignment 3: Understanding the Basics of Databases

...Understanding the Basics of Databases School University CIS 106 – Introduction to Information Technology Professor Date Understanding the Basics of Databases Databases are used to organize a collection of data, allowing for easy access, retrieval, and use of information. The first databases were slow at processing information and inconsistent when applications would require the same information, but kept in different files. Relational databases, normalization, and Structured Query Language have improved the efficiency of databases. To be a proficient database programmer, one should have a basic understanding of rational databases, normalization, Structured Query Language, and the use of database models. Relational Database The use of shared files reduced the problem with inconsistencies but was still not an efficient in accessing information. In the 1970s, a man named Ted Codd came up with the idea of the relational model for databases (Ward & Dafoulas, 2006). Similar to a spreadsheet, the relational model was based on tables, rows, and columns. Relational databases, allows the user to compare information quickly because of the logical arrangement of data in columns. The speed at which data was accessed was made possible by the development of the structured query language (SQL) query language. Because of the uniformity of relational database, completely new tables can be created from information already in existing tables. In other words, the......

Words: 931 - Pages: 4

About Databases Sql

...   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  afin  de  préciser  l’état  d’un  message  qui  pourra  être  Ouvert  ou  Clot  (dans  ce   cas  on  ne ......

Words: 1924 - Pages: 8

Database Sql

...its simplest, a database can be seen as a collection of related data. - True 2) A delimited file is a file where the length of the file is limited. - False 3) A delimited file is one in which the data is separated by a character such as a comma. - True 4) All fixed length files are of the same length. - False 5) In relational databases, logical design is kept separate from physical design. - True 6) A hierarchical database is designed with a tree-like structure that resembles a file system. - True 7) Codd defined the core principles of a relational database in 1970. - True 8) Data redundancy improves the integrity of a database. - False 9) In a diagram, crows feet notation conveys more information about the relationships. - True 10) A primary key uniquely identifies each row in a database table. - True 11) In a relational database, all data is kept in tables, even the information about the tables. - True 12) SQL is the language used by relational databases to create objects and to manipulate and retrieve data. - True 13) An entity is someone who is interested in the database. - False 14) In a statement of scope, a constraint is a limit on what the database will do. - True 15) Oracle is a popular relational database management system. 16) A relational database management system does not include tools for backing up and restoring databases. 17) Documentation is not an important part of database development.......

Words: 307 - Pages: 2

Database Notes Final

...Primary key is repeated on a second table to make a relationship it’s called… Foreign key Column or columns that uniquely identifies each row in a table … Primary key Which of the following is the best definition for a database attribute … Something that describes some part of an entity Which of the following are not common functions of relational database … Providing backup and security Which best defines data integrity … The accuracy and correctness of the data in the database Which describes what is meant by the term stakeholder … Anybody effected Document that summarizes and displays data is called a … Report Which of the following that best defines the term requirement … Something the database must do to fulfill its purpose Document that has a primary purpose of gathering data input is called … Form Which of the following defines the term exception … A variation of how things are done and recorded an alternative process Which of the following represents the advantage of a surrogate key … Surrogate key is always guaranteed to be unique Which of the following represents the advantage of a natural key … Helps protect the integrity by preventing accidental entry of duplicate rows Which of the following best defines a surrogate key … Random key with no business meaning Which of the following best defines a natural key … B ? Which of the following best defines a candidate key … D ? What type of relationship does the ERD depict … One to......

Words: 784 - Pages: 4

Sql Database

...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......

Words: 6586 - Pages: 27

Sql Tutorial Basics

...SQL Tutorial SQL TUTORIAL Simply Easy Learning by tutorialspoint.com tutorialspoint.com i ABOUT THE TUTORIAL SQL Tutorial SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language. This tutorial will give you quick start with SQL. Audience This reference has been prepared for the beginners to help them understand the basic to advanced concepts related to SQL languages. Prerequisites Before you start doing practice with various types of examples given in this reference, I'm making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language. Copyright & Disclaimer Notice All the content and graphics on this tutorial are the property of tutorialspoint.com. Any content from tutorialspoint.com or this tutorial may not be redistributed or reproduced in any way, shape, or form without the written permission of tutorialspoint.com. Failure to do so is a violation of copyright laws. This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding the accuracy of the site or its contents including this tutorial. If you discover that the tutorialspoint.com site or this tutorial content contains some errors, please contact us at webmaster@tutorialspoint.com TUTORIALS POINT Simply Easy Learning Table of Content SQL Tutorial ......................................

Words: 38789 - Pages: 156

Basic Functions of a Database for a Business

...business, an organized collection of relevant data is a critical element to a firm’s success. No matter what form of business one is involved in - whether it be a sole proprietorship, partnership, or corporation - having at least one quality database is a must if he or she expects favourable results. With effective and efficient databases, companies are able to deliver great service or produce the required units of merchandise on a timely manner. It is an established fact that running a business is no easy feat. This is especially true for a relatively new one, which is still struggling to make a name for itself while trying to generate more capital for its operations. Because of the sheer number of challenges an emerging proprietor faces, he or she needs as much help as he/she can have when it comes to managing his/her business. Databases provide great help for this very problem. Listed below are the several purposes databases generally try to serve to a typical business: * It helps organize important information – information is a key factor to any business, and having systematically arranged records help the user know what needs to be done, how it must be done, and who it is being done for. * It helps save time – with a database, instead of spending precious time going through piles of papers just to search for the necessary information, the user is just a query away from retrieving what is needed. * It aids the communication among employees – due to......

Words: 489 - Pages: 2

Sql Database

...1. DBMS: DataBase Management System. 2. A shared integrated computer structure that houses a collection of related data. DBMS is the software product and Database is to create and maintain the software. 3. a) Handle all physical structure b) - Often used by multiple people (views) - Concurrency control (Accessing/ updating simultaneously) 4. A DBMS component that stores metadata- data about data. Thus the data dictionary contains the data definition as well as their characteristics and relationships. A data dictionary may also include data that are external to the DBMS. 5. - redundancy -promotes consistency -handles concurrency - data integrity 6. – Cost: product itself, more expensive hardware - Complexity: Only accessible via DBMS 7. a)Table: A matrix composed of intersecting rows and columns that represents an entity set in the relational model b) File: A collection of related records c) Record: A logically connected set of one or more fields that describes a person place or thing d) Row: where we record all the data e) Attribute: where we put the names and characteristics f) Field: A character of group of characters that has a specific meaning g) Columns: Where we put of the attributes 8. it will direct into lack of design and data modeling skills, also it will be a lot of data redundancy which is not good for the database. 9. a) Seven records b) Five fields c) -addresses are only from FL and TN ...

Words: 387 - Pages: 2

Cis 515 Week 6 Assignment Sql Concepts and Database Design

...CIS 515 Week 6 Assignment SQL Concepts and Database Design http://www.tutoriallance.com/shop/uncategorized/cis-515-week-6-assignment-sql-concepts-and-database-design/ For any inquiry email us: Tutoriallance@gmail.com Visit Website For More Tutorials : http://www.tutoriallance.com/ SQL Concepts and Database Design Due Week 7 and worth 90 points The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server. Imagine that you work for a finance industry-based organization. Your organization is looking to submit its database design documentation to an evaluation team in order to meet Sarbanes-Oxley (SOX) compliance. You have been assigned to assist in preparing the documentation of the organization’s sales database. You are asked to propose a data dictionary that can be used to document the following: • Employee (EmpNumber , EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID) • Invoice ( InvNumber , InvDate, EmpNumber, InvAmount, CustomerID) • InvoiceLine ( InvLineNumber , InvNumber, ProductNumber, Quantity) • Product (ProductNumber , ProductDescription, ProductCost) • Department (DepartmentID , DepartmentDescription) • Job (JobID , JobDescription) • Customer (CustomerID , CustomerName, CustomerAddress, CustomerPhone) Write a three to four (3-4) page paper in which you: 1. Create a data......

Words: 435 - Pages: 2

Cis 515 Week 6 Assignment Sql Concepts and Database Design

...CIS 515 Week 6 Assignment SQL Concepts and Database Design http://www.tutoriallance.com/shop/uncategorized/cis-515-week-6-assignment-sql-concepts-and-database-design/ For any inquiry email us: Tutoriallance@gmail.com Visit Website For More Tutorials : http://www.tutoriallance.com/ SQL Concepts and Database Design Due Week 7 and worth 90 points The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server. Imagine that you work for a finance industry-based organization. Your organization is looking to submit its database design documentation to an evaluation team in order to meet Sarbanes-Oxley (SOX) compliance. You have been assigned to assist in preparing the documentation of the organization’s sales database. You are asked to propose a data dictionary that can be used to document the following: • Employee (EmpNumber , EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID) • Invoice ( InvNumber , InvDate, EmpNumber, InvAmount, CustomerID) • InvoiceLine ( InvLineNumber , InvNumber, ProductNumber, Quantity) • Product (ProductNumber , ProductDescription, ProductCost) • Department (DepartmentID , DepartmentDescription) • Job (JobID , JobDescription) • Customer (CustomerID , CustomerName, CustomerAddress, CustomerPhone) Write a three to four (3-4) page paper in which you: 1. Create a data......

Words: 435 - Pages: 2

Short Notes on Basic Developmental Psychology

...* Introduction to developmental psychology (history, basic issues) * Theories of development * Research methods in child development Introduction History Child development as a science Parental preoccupation with “expert” child rearing started in the early 20th century. Parents started turning to paediatricians and psychologists for advice. John Watson pushed for rigid feeding schedules for infants and an orderly approach to child rearing. Benjamin Spock’s urged parents not engage in conflicts over issues such as weaning and toilet training, and to display affection to their children. Today Watson’s views are seen as emotionally cold and excessively rigid, while Spock’s recommendations as overly indulgent. What does developmental psychology study? Developmental science seeks to identify variables that influence development and to explain how they work together to shape an individual’s life. Scientists develop theories and conduct research aimed at describing, explaining, and predicting age-related changes in behaviour, thinking, emotions and social relationships. Definition Orderly and relatively enduring changes over time in physical and neurological structures, thought processes, and behaviour. 3 broad goals in the study of 3 child development: * To understand changes that appear to be universal * To explain individual differences * To understand how children’s behaviour is influenced by the environmental context or situation.......

Words: 826 - Pages: 4

Cis 515 Assignment 6 Sql Concepts and Database Design

...CIS 515 Assignment 6 SQL Concepts and Database Design http://homeworklance.com/downloads/cis-515-assignment-6-sql-concepts-and-database-design/ The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server. Imagine that you work for a finance industry-based organization. Your organization is looking to submit its database design documentation to an evaluation team in order to meet Sarbanes-Oxley (SOX) compliance. You have been assigned to assist in preparing the documentation of the organization’s sales database. You are asked to propose a data dictionary that can be used to document the following: • Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID) • Invoice (InvNumber, InvDate, EmpNumber, InvAmount, CustomerID) • InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity) • Product (ProductNumber, ProductDescription, ProductCost) • Department (DepartmentID, DepartmentDescription) • Job (JobID, JobDescription) • Customer (CustomerID, CustomerName, CustomerAddress, CustomerPhone) Write a three to four (3-4) page paper in which you: 1. Create a data dictionary that includes the following: 1. A description of the content for each field 2. The data type of each field 3. The format the data will be stored as in the field 4. The range of value for the field 5....

Words: 398 - Pages: 2

Cis 515 Assignment 6 Sql Concepts and Database Design

...CIS 515 Assignment 6 SQL Concepts and Database Design http://homeworklance.com/downloads/cis-515-assignment-6-sql-concepts-and-database-design/ The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server. Imagine that you work for a finance industry-based organization. Your organization is looking to submit its database design documentation to an evaluation team in order to meet Sarbanes-Oxley (SOX) compliance. You have been assigned to assist in preparing the documentation of the organization’s sales database. You are asked to propose a data dictionary that can be used to document the following: • Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID) • Invoice (InvNumber, InvDate, EmpNumber, InvAmount, CustomerID) • InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity) • Product (ProductNumber, ProductDescription, ProductCost) • Department (DepartmentID, DepartmentDescription) • Job (JobID, JobDescription) • Customer (CustomerID, CustomerName, CustomerAddress, CustomerPhone) Write a three to four (3-4) page paper in which you: 1. Create a data dictionary that includes the following: 1. A description of the content for each field 2. The data type of each field 3. The format the data will be stored as in the field 4. The range of value for the field 5....

Words: 398 - Pages: 2

No Sql Databases

...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......

Words: 4246 - Pages: 17