1 / 26

Introduction to sql server and sql

Introduction to sql server and sql. Chapters 1, 15, 16, 17 G. Green. Agenda. History of SQL SQL Server Overview Accessing SQL Server Databases Tables Data Values Summary. History of SQL. Structured Query Language (SQL) Conceptualized in early '70s Prototyped by IBM in mid '70s

warren
Download Presentation

Introduction to sql server and sql

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to sql server and sql Chapters 1, 15, 16, 17 G. Green

  2. Agenda • History of SQL • SQL Server Overview • Accessing SQL Server • Databases • Tables • Data Values • Summary

  3. History of SQL • Structured Query Language (SQL) • Conceptualized in early '70s • Prototyped by IBM in mid '70s • Standard language for creating/manipulating relational structures • Mostly same, but… • Many vendors extend standard

  4. What is sql server? • Database Management System • Relational DBMS • XML • OLAP • Services • Query • Full Text Search • BI • Integration • Analysis • Reporting • Development Tools • SQLCMD • SQL Server Management Studio (SSMS) • Business Intelligence Management Studio (BIDS) • Visual Studio

  5. Sql server Architecture Business User/ Application Client Database Engine Server Non SQL Server Databases Local or Remote SQL Server Databases System Databases User Databases master temp Oracle Server DB mis3350 Ticket sales nonprofit

  6. Accessing sql server • See Installing SQL Server for how to: • (Optional) Install SQL Server Express Edition • With Management Studio • Log in to SQL Server

  7. SQL Commands • Data Definition Language (DDL) • Define/change/remove database objects • Access to objects • Data Manipulation Language (DML) • Maintain and query data in tables • Internet Resource • http://www.w3schools.com/sql/default.asp

  8. DDL • Creating Objects (structures) • Databases • Create database • Data structures • Create tables • Database Users • Create logins and users • Database access rights • Permissions to use structures • Maintaining Structures and Access to Structures • Alter • Drop or Revoke

  9. Creating databases manually • Computer file, physical container for objects • CREATE DATABASE dbname; • Example: • CREATE DATABASE crmclass;

  10. Creating databases visually

  11. Creating tables manually • Object/Structure that stores data • CREATE TABLEtable_name (column1 data_type [(length)], column2 data_type [(length)], …);

  12. Data types • There are dozens! • Common ones: • Varchar for text data (letters and/or numbers) • Numeric  for numeric data • Date  for calendar dates

  13. Create tableexample CREATE TABLE student (stud_id numeric (9), pin varchar(20), firstnamevarchar(50), lastnamevarchar(50), addrvarchar(50), classification varchar(10), gmat_score numeric (4), sat_score numeric (4));

  14. Creating tables visually • Right-click Tables folder • New Table… • Enter column names, and data types & lengths • Click Save icon • Give table a name

  15. Establishing Integrity, relationships, validity checks • For each table, specify: • Primary Key must do this! • Foreign Key  if table links to another table • Checks  for valid column values • Not Null  if any column values are required

  16. constraintsexample • CREATE TABLE registration • (crn numeric (5) NOT NULL, • stud_idnumeric (9) NOT NULL, • regn_datedate NOT NULL, • final_grade numeric (3,2), • CONSTRAINT reg_pkPRIMARY KEY (crn, stud_id), • CONSTRAINT final_grade_ckCHECK (final_gradebetween 0.00 and 4.00) • CONSTRAINT reg_fk2 FOREIGN KEY (stud_id)REFERENCES student (stud_id));

  17. Creating constraints visually PK constraint Required or Optional Check constraint

  18. Creating fk’s visually, cont… FK Constraint

  19. Creating database Users • Individuals (or programs) who have direct access to the database • Steps • Create Login • Allows user to connect to the SQL Server instance • Create User • Allows user to access a specific DB in SQL Server • Grant Permissions • Gives users specific rights within a DB

  20. DML • Inserting Data • Updating Data • Deleting Data • Querying Data • Single Table • Multiple Tables

  21. Inserting Data manually • INSERT INTO table_name [(column1_name, column2_name, …)] VALUES (column1_value, column2_value, …); • Examples: INSERT INTO Student VALUES (123456789, 'mypin', 'jane', 'doe', '999 main st. apt 899b', 'junior',NULL,1350); INSERT INTO Student (stud_id, firstname, addr, sat_score) VALUES (123456777, 'jane', '123 main st.', 1350); INSERT INTO Registration VALUES (1111, 123456789, '31-JAN-2010', 3.4));

  22. Inserting data visually

  23. Deleting Data manually • DELETE FROM table_name [WHERE condition(s)]; • Examples: DELETE FROM registration;  deletes all rows DELETE FROM student deletes specific WHEREfirstname= 'jane'; rows

  24. Deleting data visually

  25. summary • What is SQL Server? • Architecture • What is SQL? • Accessing SQL Databases • Creating Objects • Databases • Tables • Users • Working with Data • Inserting Records • Deleting Records

  26. Next Time… • Querying Data (SQL Ch. 2 – 13) • 2/6 – 2/25 • *** Assignment 1 DUE *** • 2/11

More Related