SQL 2 books in 1 - The Ultimate Beginners Intermediate Guide to Learn SQL Programming step by step (Ryan Turner) (Z-Library)

📄 File Format: PDF

💾 File Size: 3.6 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

SQL: 2 BOOKS IN 1 The Ultimate Beginner's & Intermediate Guide to Learn Python Machine Learning Step by Step using Scikit-Learn and Tensorflow

📄 Page 3

RYAN TURNER

📄 Page 4

© Copyright 2018 - All rights reserved. The content contained within this book may not be reproduced, duplicated or transmitted without direct written permission from the author or the publisher. Under no circumstances will any blame or legal responsibility be held against the publisher, or author, for any damages, reparation, or monetary loss due to the information contained within this book. Either directly or indirectly. Legal Notice: This book is copyright protected. This book is only for personal use. You cannot amend, distribute, sell, use, quote or paraphrase any part, or the content within this book, without the consent of the author or publisher. Disclaimer Notice: Please note the information contained within this document is for educational and entertainment purposes only. All effort has been executed to present accurate, up to date, and reliable, complete information. No warranties of any kind are declared or implied. Readers acknowledge that the author is not engaging in the rendering of legal, financial, medical or professional advice. The content within this book has been derived from various sources. Please consult a licensed professional before attempting any techniques outlined in this book. By reading this document, the reader agrees that under no circumstances is the author responsible for any losses, direct or indirect, which are incurred as a result of the use of information contained within this document, including, but not limited to, — errors, omissions, or inaccuracies.

📄 Page 5

TABLE OF CONTENTS SQL: THE ULTIMATE BEGINNER'S GUIDE Introduction 1. Advantages Of Databases 2. Types Of Sql Queries Chapter 1: The Data Definition Language (Ddl) 3. Ddl For Database And Table Creation 4. Alter Ddl For Foreign Key Addition 5. Foreign Key Ddl In Tables 6. Unique Constraint Ddl In Tables 7. Delete And Drop Ddl In Tables 8. Ddl To Create Views Chapter 2: Sql Joins And Union 1. Sql Inner Join 2. Sql Right Join 3. Sql Left Join 4. Sql Union Command 5. Sql Union All Command Chapter 3: How To Ensure Data Integrity Integrity Constraints – The Basics The Not Null Constraint The Unique Constraint The Primary Key Constraint The Foreign Key Constraints The Check Constraint Chapter 4: How To Create An Sql View How To Add A View To A Database How To Create An Updateable View How To Drop A View Database Security The Security Model Of Sql Creating And Deleting A Role Granting And Revoking A Privilege Chapter 5: Database Creation Creating A Database Removing A Database Schema Creation Creating Tables And Inserting Data Into Tables How To Create A Table Creating A New Table Based On Existing Tables

📄 Page 6

Inserting Data Into Table Populating A Table With New Data Inserting Data Into Specific Columns Inserting Null Values Data Definition Language (Ddl) Running The Ddl Script Data Manipulation Language (Dml) Running The Dml Script Chapter 6: Database Administration Chapter 7: Sql Transaction Chapter 8: Logins, Users And Roles Like Clause Sql Functions Sql Avg Function Sql Round Function Sql Sum Function Sql Max() Function Chapter 9: Modifying And Controlling Tables Modifying Column Attributes Renaming Columns Deleting A Column Adding A New Column Modifying An Existing Column Without Changing Its Name Rules To Remember When Using Alter Table Deleting Tables Combining And Joining Tables Sql Character Functions Note: Sql Constraints Not Null Constraint Default Constraint Unique Constraint Primary Key Foreign Key Check Constraint Index Constraint Conclusion And Next Steps References SQL: THE ULTIMATE INTERMEDIATE GUIDE Introduction Chapter 1: The Simple Basics Of Sql What Is Sql?

📄 Page 7

Working On The Databases Relational Database Client And Server Technology Internet-Based Database Systems Chapter 2: Installing And Configuring Mysql What Is Mysql? How To Install Mysql On Microsoft Windows On Your Computer Chapter 3: The Sql Server Installing Oracle Database 11g Express Edition Installing Sql Developer Creating A System Connection Creating A Database Account Creating Your Account Connection Showing Line Numbers Deleting The System Connection Using Sql Developer Running An Sql Statement Inserting Rows Running A Pl/Sql Program Multiple Worksheets For A Connection Storing The Source Code How To Open A Source Code Storing The Listings In Appendix A In Files Running Sql Or Pl/Sql From A File Clearing A Worksheet Displaying Output Clearing Dbms_Output Chapter 4: Data Types Categories Of Data Types Character Number Date And Time Arithmetic Chapter 5: Sql Script Boundaries Dropping Restricts Integrity Restricts Delegating Responsibility Chapter 6: Filters Where Clause Having Clause Evaluating A Condition And Operator Or Operator Usage Of Parentheses The Not Operator

📄 Page 8

Sequences Renumbering A Sequence Chapter 7: Views Encrypting A View Creating A View Indexing A View Chapter 8: Triggers Trigger Syntax After Trigger Instead Of Trigger Chapter 9: Stored Routines And Functions In Sql Stored Procedures Benefits Offered By Stored Procedures In Sql Creating A Stored Procedure Executing A Stored Procedure Inserting Records Updating Records Deleting Records Modifying A Stored Procedure Deleting A Stored Procedure Functions Scalar Functions Table-Valued Functions Notes On Functions Cursors Chapter 10: How To Pivot Data How Can You Compose Your Pivot Query? Chapter 11: Clone Tables How To Create A Clone Table Purposes Of Clone Tables Chapter 12: Normalization Of Your Data How To Normalize The Database Raw Databases Logical Design The Needs Of The End User Data Repetition Normal Forms Naming Conventions Benefits Of Normalizing Your Database Denormalization Database Normal Forms First Normal Form (1nf) Second Normal Form (2nf) Third Normal Form (3nf)

📄 Page 9

Boyce-Codd Normal Form (Bcnf) Fourth Normal Form (4nf) Fifth Normal Form (5nf) Chapter 13: Security Components Of Database Security Authentication Encryption Authorization Access Control Three-Class Security Model Schemas Server Roles Logins Mixed Mode Authentication Database Roles Encryption Master Keys Transparent Data Encryption (Tde) Chapter 14: Sql Injections How Do They Work? Preventing An Sql Injection Like Quandary Hacking Scenario Chapter 15: Fine-Tune Your Indexes Sql Tuning Tools Chapter 16: Deadlocks Deadlock Analysis And Prevention Reading Deadlock Info Via Sql Server Error Log Some Tips For Avoiding Deadlock Chapter 17: Database Administration Setting Up A Maintenance Plan In Sql Server Define Backup Database (Full) Task Define Database Check Integrity Task Define Shrink Database Task Define Reorganize Index Task Define Rebuild Index Task Define Update Statistics Task Define History Cleanup Task Define Backup Database (Differential) Task Define Backup Database (Transaction Log) Task Define Execute Sql Server Agent Job Task Define Maintenance Cleanup Task Report Options Complete The Wizard Running The Maintenance Plan

📄 Page 10

Emailing The Reports Configuring The Database Mail Sql Server Agent Backup And Recovery The Transaction Log Recovery Changing The Recovery Model Backups Performing A Backup Restoring A Database Attaching And Detaching Databases Chapter 18: Working With Ssms Downloading Sql Server Management Studio (Ssms) Starting The Database Engine Services Connect To Sql Server With Ssms The Basics And Features Of Ssms Managing Connections Choosing Your Database New Query Window Executing Statements Intellisense Results Presentation Object Explorer Databases Chapter 19: Real-World Uses Sql In An Application Conclusion References

📄 Page 11

SQL THE ULTIMATE BEGINNER'S GUIDE TO LEARN SQL PROGRAMMING STEP BY STEP

📄 Page 12

(This page has no text content)

📄 Page 13

(This page has no text content)

📄 Page 14

INTRODUCTION “Never write when you can talk. Never talk when you can nod. And never put anything in an e-mail.” – Eliot Spitzer On a hard disk, data can be stored in different file formats. It can be stored in the form of text files, word files, mp4 files, etc. However, a uniform interface that can provide access to different types of data under one umbrella in a robust and efficient manner is required. Here, the role of databases emerges. The definition of a database is “a collection of information stored in computer in a way that it can easily be accessed, managed and manipulated.” Databases store data in the form of a collection of tables where each table stores data about a particular entity. The information that we want to store about students will be represented in the columns of the table. Each row of the table will contain the record of a particular student. Each record will be distinguished by a particular column, which will contain a unique value for each row. Suppose you want to store the ID, name, age, gender, and department of a student. The table in the database that will contain data for this student will look like this: SID SName SAge SGender SDepartment 1 Tom 14 Male Computer 2 Mike 12 Male Electrical 3 Sandy 13 Female Electrical 4 Jack 10 Male Computer 5 Sara 11 Female Computer Student Table Here, the letter “S” has been prefixed with the name of each column. This is just one of the conventions used to denote column names. You can give any name to the columns. (We will look at how to create tables and columns within it in the coming chapters.) It is much easier to access, manipulate, and manage data stored in this form. SQL queries can be executed on the data stored in the form of tables that have relationships with other tables. A database doesn’t contain a single table. Rather, it contains multiple related tables. Relationships maintain database integrity and prevent data redundancy.

📄 Page 15

For instance, if the school decides to rename the Computer department from “Computer” to “Comp & Soft,” you will have to update the records of all students in the Computer department. You will have to update the 1st, 4th, and 5th records of the student table. It is easy to update three records; however, in real life scenarios, there are thousands of students and it is an uphill task to update the records of all of them. In such scenarios, relationships between data tables become important. For instance, to solve the aforementioned redundancy problem, we can create another table named Department and store the records of all the departments in that table. The table will look like this: DID DName DCapacity 101 Electrical 800 102 Computer 500 103 Mechanical 500 Department Table Now, in the student table, instead of storing the department name, the department ID will be stored. The student table will be updated like this: SID SName SAge SGender DID 1 Tom 14 Male 102 2 Mike 12 Male 101 3 Sandy 13 Female 101 4 Jack 10 Male 102 5 Sara 11 Female 102 Table Student You can see that the department name column has been replaced by the department id column, represented by “DID”. The 1st, 4th, and 5th rows that were previously assigned the “Computer” department now contain the id of the department, which is 102. Now, if the name of the department is changed from “Computer” to “Comp & Soft”, this change has to be made only in one record of the department table and all the associated students will be automatically referred to the updated department name. 1. Advantages of Databases

📄 Page 16

1. Advantages of Databases The following are some of the major advantages of databases: Databases maintain data integrity. This means that data changes are carried out at a single place and all the entities accessing the data get the latest version of the data. Through complex queries, databases can be efficiently accessed, modified, and manipulated. SQL is designed for this purpose. Databases avoid data redundancy. Through tables and relationships, databases avoid data redundancy and data belonging to particular entities resides in a single place in a database. Databases offer better and more controlled security. For example, usernames and passwords can be stored in tables with excessive security levels. 2. Types of SQL Queries On the basis of functionality, SQL queries can be broadly classified into a couple of major categories as follows: Data Definition Language (DDL) Data Definition Language (DDL) queries are used to create and define schemas of databases. The following are some of the queries that fall in this category: 1. CREATE – to create tables and other objects in a database 2. ALTER – to alter database structures, mainly tables. 3. DROP - delete objects, mainly tables from the database Data Manipulation Language Data Manipulation Language (DML) queries are used to manipulate data within databases. The following are some examples of DML queries. 1. SELECT – select data from tables of a database 2. UPDATE - updates the existing data within a table 3. DELETE - deletes all rows from a table, but the space for the record remains

📄 Page 17

(This page has no text content)

📄 Page 18

CHAPTER 1: THE DATA DEFINITION LANGUAGE (DDL) “Every man has a right to his opinion, but no man has a right to be wrong in his facts. “– Bernard Mannes Baruch SQL data definition language is used to define new databases, data tables, delete databases, delete data tables, and alter data table structures with the following key words; create, alter and drop. In this chapter, we will have a detailed discussion about the SQL Data Definition Language in a practical style. 3. DDL for Database and Table Creation The database creation language is used to create databases in a database management system. The language syntax is as described below: CREATE DATABASE my_Database For example, to create a customer_details database in your database management system, use the following SQL DDL statement: CREATE DATABASE customer_details Please remember that SQL statement is case insensitive. Next, we need to create the various customer tables that will hold the related customers records, in the earlier created ‘customer_details’ database. This is why the system is called a relational database management system, as all tables are related for easy record retrieval and information processing. To create the different but related customer tables in the customer_details database, we apply the following DDL syntax: CREATE TABLE my_table ( table_column-1 data_type, table_column-2 data_type, table_column-3 data_type, table_column-n data_type

📄 Page 19

) CREATE TABLE customer_accounts ( acct_no INTEGER, PRIMARY KEY, acct_bal DOUBLE, acct_type INTEGER, acct_opening_date DATE )[1] The attribute “PRIMARY KEY” ensures the column named ‘acct_no’ has unique values throughout, with no null values. Every table should have a primary key column to uniquely identify each record of the table. Other column attributes are ‘NOT NULL’ which ensures that a null value is not accepted into the column, and ‘FOREIGN KEY’ which ensures that a related record in another table is not mistakenly or unintentionally deleted. A column with a ‘FOREIGN KEY’ attribute is a copy of a primary key column in another related table. For example, we can create another table ‘customer_personal_info’ in our ‘customer_details’ database like below: CREATE TABLE customer_personal_info ( cust_id INTEGER PRIMARY KEY, first_name VARCHAR(100) NOT NULL, second_name VARCHAR(100), lastname VARCHAR(100) NOT NULL, sex VARCHAR(5), date_of_birth DATE, address VARCHAR(200) )[2] The newly created ‘customer_personal_info’ table has a primary key column

📄 Page 20

named ‘cust_id’. The ‘customer_accounts’ table needs to include a column named ‘cust_id’ in its field to link to the ‘customer_personal_info’ table in order to access the table for more information about the customer with a given account number. Therefore, the best way to ensure data integrity between the two tables, so that an active record in a table is never deleted is to insert a key named ‘cust_id’ in the ‘customer_accounts’ table as a foreign key. This ensures that a related record in ‘customer_personal_info’ to another in ‘customer_accounts’ table is never accidentally deleted. We will discuss how to go about this in the next section. 4. Alter DDL for Foreign Key Addition Since ‘customer_accounts’ table is already created, we need to alter the table to accommodate the new foreign key. To achieve this, we use the SQL Data Definition Language syntax described below: ALTER TABLE mytable ADD FOREIGN KEY (targeted_column) REFERENCES related_table(related_column) Now, to add the foreign key to the the ‘customer_accounts’ table and make it reference the key column ‘cust_id’ of table ‘customer_personal_info’, we use the following SQL statements: ALTER TABLE customer_accounts ADD FOREIGN KEY (cust_id) REFERENCES customer_personal_info(cust_id) 5. Foreign Key DDL in Tables In situations where we need to create foreign keys as we create new tables, we make use of the following DDL syntax: CREATE TABLE my_table ( Column-1 data_type FOREIGN KEY, REFERENCES (related column)