SQL Full Database Course for Beginners Part 1 written by sarjunsafeen The Marketplace to Buy and Sell your Study Material At Stuvia you will find the best notes summaries flashcards other study material Search for your school or uni and find the study material you need www stuvia com Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year Stuvia com The Marketplace to Buy and Sell your Study Material Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year SQL Basics Composite Key Language Types and Access ControlComposite Key Concept and Importance A composite key is a combination of two or more columns in a table that uniquely identifies each row Composite keys are used when a single column is not sufficient to providea unique identifier for a row Data Insertion and Structure in Tables In SQL data is organized into tables consisting of rows and columns Each column represents a field and each row represents a record Updating Deleting and Selecting Data In depth Use the UPDATE statement to modify records Use the DELETE statement to remove records Use the SELECT statement to retrieve records Installing and Using PopSQL PopSQL is a graphical SQL editor Download and install PopSQL for visual learning Understanding SQL Datatypes SQL has various data types including INTEGER FLOAT TEXT DATEDatabase Table Creation and Manipulation Use the CREATE TABLE statement to create a new table Part 1Page 1 Stuvia com The Marketplace to Buy and Sell your Study Material Stuvia com The Marketplace to Buy and Sell your Study Material Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year Use the ALTER TABLE statement to modify an existing table Control of Data Entry and Table Structure Use constraints to control data entry and maintain table structure NOT NULL PRIMARY KEY FOREIGN KEY CHECKConfiguring MySQL Database Setup and Configuration To set up a MySQL database Download and install MySQL Server Install the MySQL Command Line Interface Restart the system Creating and Connecting to Databases Use the CREATE DATABASE statement to create a new database Use the USE statement to select a specific database Database Structure Basic Concepts A database may contain several interconnected tables Basic structural elements Branches Clients ManagerSQL Queries Writing and Executing Commands Use the SELECT statement combined with various clauses and operators to query the database Part 1Page 2 Stuvia com The Marketplace to Buy and Sell your Study Material Stuvia com The Marketplace to Buy and Sell your Study Material Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year Creating Tables and Database Schema Use the CREATE TABLE statement to create a table Define columns data types constraints and relationships Database Structure Employee Client Relationship Employee table Unique identifier Basic information Client table Unique identifier Basic informationTable Branch Store branch information Columns unique ID branch name location Query Example Select all employees ordered by salary Example SELECT FROM Employee ORDER BY salary ASC Using PopSQL SQL Text Editor for Visual Learning Use PopSQL as a user friendly text editor for writing and executing SQL commands Employee Salaries Average Sum and Distribution Use aggregate functions such as AVG average SUM sum and COUNT quantity Constraints for Ensuring Data Quality and UniquenessPart 1Page 3 Stuvia com The Marketplace to Buy and Sell your Study Material Stuvia com The Marketplace to Buy and Sell your Study Material Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year NOT NULL PRIMARY KEY FOREIGN KEY CHECKDatabase Schema Company Database Companies have managers branches suppliers clients and employees Employees Information Number Gender and BirthYear Saved as columns within the employee table Branch Suppliers in Label Business Store information about suppliers for specific branches Clients Branch Suppliers Names Store client names and specific branch supplier information Table Client Store important client information Company Structure Salesman and Client Relationship Explain the complexity of the company structure and highlight various relationships Employees Born in October Using the MONTH function to retrieve employee birthdays in October Part 1Page 4 Stuvia com The Marketplace to Buy and Sell your Study Material Stuvia com The Marketplace to Buy and Sell your Study Material Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year Part 1Page 5 Stuvia com The Marketplace to Buy and Sell your Study Material Stuvia com The Marketplace to Buy and Sell your Study Material Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year Part 1Page 6 Stuvia com The Marketplace to Buy and Sell your Study Material Stuvia com The Marketplace to Buy and Sell your Study Material Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year Configuring MySQL Database Setup and Configuration MySQL Database Management System Setup Installing MySQL Setting up MySQL database Creating and Connecting to DatabasesPart 1Page 7 Stuvia com The Marketplace to Buy and Sell your Study Material Stuvia com The Marketplace to Buy and Sell your Study Material Downloaded by sarjunsafeen sarjunsafeen1 gmail com Distribution of this document is illegal Want to earn 1 236 extra per year Creating new databases Connecting to databases Database Structure Branch and Client Connection Managers and Branches Employee Client Relationship Suppliers and BranchesDatabase Table Creation and Manipulation Table Employee Columns Employee information number gender birth year Table Branch Columns Branch suppliers in label business Table Client Columns Clients branch suppliers namesData Insertion and Structure in Tables Composite Key Concept and Importance Using composite keys to ensure uniqueness and data quality Employee Information Number Gender and Birth Year Inserting employee information into the Employee tablePart 1Page 8 Stuvia com The Marketplace to Buy and Sell your