C2 - Web Applications for Everybody Specialization - COURSE 2

 Introduction to Structured Query Language (SQL)

LECTURES COURSE 2

Subscribe to more Courses.
COLLABORATOR CHANNELS
YOUCOURSE - COURSES FROM BEST UNIVERSITYS IN THE WORLD - https://www.youtube.com/@YouCourse
ARQUITECTURAS - ARCHITECTURE CHANNEL, DOCUMENTALS AND MORE - https://www.youtube.com/@ArquitecturasYT
CADESIGNERS - SOFTWARE CHANNEL AND MODELS - https://www.youtube.com/@CADesigners
UNDERGROUND - Subscribe! Free Music - Creative Commons - https://www.youtube.com/@UndergroundMC

FACEBOOK GROUPS
ARQUITECTURAS - ARCHITECTURE CHANNEL - https://www.facebook.com/groups/arquitecturasyt
YOUCOURSE - COURSES FROM BEST UNIVERSITYS IN THE WORLD - https://www.facebook.com/groups/youcourse/
CADESIGNERS - SOFTWARE DESIGN CHANNEL - https://www.facebook.com/groups/cadesignersyt
UNDERGROUND - Free Music - Creative Commons - https://www.facebook.com/groups/undergroundyt

Facebook - https://www.facebook.com/YouCourseYT
Twitter - https://twitter.com/YouCourseYT
Tik Tok - https://www.tiktok.com/@youcourseyt
Kwai - YouCourse - @yourcourse

Thank's For Support!

WEEK 1 - Installing PHP and SQL

Our first technical task is to work through the installation steps including installing a text editor, installing MAMP or XAMPP (or equivalent), creating a MySql Database.

00:00 - Welcome to the Course

3:22 - Installing MAMP on a Macintosh

10:12 - Installing MAMP on Windows 10

24:34 - Installing XAMPP on Windows 10

33:39 - Bonus: Office Hours in the Hague

WEEK 2 - Introduction to Structured Query Language (SQL)

We learn about single table queries and the basic syntax of the SQL language.

37:16 - Overview of Databases

52:19 - Basic SQL Operations

1:14:05 - Data Types in SQL

1:22:38 - Database Keys and Indexes

1:35:50 - Bonus: Liz Fong - Standardizing SQL

1:43:13 - Bonus: Office Hours Orlando, Florida

WEEK 3 - Database Design

Covering database design with multiple tables, foreign keys, and the JOIN operation.

1:45:49 - Relational Database Design

2:00:10 - Normalization and Foreign Keys

2:09:24 - Building a Physical Data Schema

2:26:48 - Relational Database Design #41

2:40:31 - Bonus: Office Hours Houston, TX

2:45:19 - Fun: Dr. Chuck gets a Educational Technology Tattoo

WEEK 4 - Many-To-Many

We learn how to model many-to-many relationships like those needed to represent users, roles, and courses.

2:46:29 - Many-to-Many Relationships

3:00:50 - Bonus: Office Hours Portland, Oregon

SEE YOU IN THE COURSE 3 - Building Database Applications in PHP -  THANK'S FOR WATCHING

Don't forget to subscribe and like that helps me keep uploading videos

Thank's For Support!

University of Michigan
The mission of the University of Michigan is to serve the people of Michigan and the world through preeminence in creating, communicating, preserving and applying knowledge, art, and academic values, and in developing leaders and citizens who will challenge the present and enrich the future.

About this Specialization
This Specialization is an introduction to building web applications for anybody who already has a basic understanding of responsive web design with JavaScript, HTML, and CSS. Web Applications for Everybody is your introduction to web application development. You will develop web and database applications in PHP, using SQL for database creation, as well as functionality in JavaScript, jQuery, and JSON.

Over the course of this Specialization, you will create several web apps to add to your developer portfolio. This Specialization (and its prerequisites) will prepare you, even if you have little to no experience in programming or technology, for entry level web developer jobs in PHP.

You’ll demonstrate basic concepts, like database design, while working on assignments that require the development of increasing challenging web apps. From installing a text editor to understanding how a web browser interacts with a web server to handling events with JQuery, you’ll gain a complete introductory overview of web application development.

About this Course
In this course, you'll walk through installation steps for installing a text editor, installing MAMP or XAMPP (or equivalent) and creating a MySql Database. You'll learn about single table queries and the basic syntax of the SQL language, as well as database design with multiple tables, foreign keys, and the JOIN operation. Lastly, you'll learn to model many-to-many relationships like those needed to represent users, roles, and courses.

WEEK 1 - Installing PHP and SQL

Our first technical task is to work through the installation steps including installing a text editor, installing MAMP or XAMPP (or equivalent), creating a MySql Database.

RESOURCES

"The Missing Link" Textbook Chapter: 1, 3, 4, 37, 39, 41
Installation Handouts

In this page, you will install a programmer text editor, install a bundled all-in-one PHP/MySql application, and explore that application briefly.

We have separate pages for each of the commonly used Operating Systems:

• Setting up the MAMP PHP/MySql Environment on a Macintosh
• Setting up the MAMP PHP/MySql Environment on a Windows
• Setting up the XAMPP PHP/MySql Environment in Microsoft Windows

I've done most of my demos using MAMP on the Macintosh so using MAMP might be easier on Windows. If you encounter a problem getting MAMP to work on Windows, it is OK to use XAMPP.

Please note, MySQL Workbench is not supported for this specialization.

Note - You only need to install one of MAMP or XAMPP. Do not install both as they use the same port settings. They are both all-in-one installers for the same thing - Apache Web Server, MySQL, PHP, phpMyAdmin.

Textbook & Course Resources

This course uses an open and free textbook titled, "The Missing Link: An Introduction to Web Development and Programming".

https://milneopentextbooks.org/the-missing-link-an-introduction-to-web-development-and-programming/

Please utilize the Resources section for downloadable slides, web links, and other resources by module.

Installing LAMP On Linux

We don't have a video about how to install the software on Linux since there are many different Linux systems and most Linux users already know how to install software. In the Linux world, what we call "MAMP" or "XAMPP" is called "LAMP". Actually LAMP came first and the XAMPP / MAMP names are homage to the original Linux / LAMP.

A simple approach is to search for "How to install LAMP on Ubuntu" - You should find lots of pages. If we find good guides, we will list them here:

Ask Ubuntu: How to set up a LAMP stack

Assignment Specifications: Installing PHP/MySQL

In this assignment you will install software that will be used during the course. This software includes a programming text editor and a PHP and MySQL Environment. Also as part of this assignment you will learn how to take screenshots and make them into images. The deliverables of this assignment are two peer-graded images of your running software.

If you follow the instructions carefully and look at the handouts and watch the podcasts, this assignment should be pretty straightforward and take less than an hour.

Note: As you install on Windows, you may be asked for permission to open ports or to allow an application through the firewall - say 'yes' to these dialog boxes if they are popping up when you first install or first start your PHP environment.

If you get stuck on this assignment - feel free to get assistance from anyone who can help. All we want is that you have successfully installed the software and can use it on your system. This is not intended as a difficult or challenging assignment.

You should practice taking a few screen shots on your computer and saving them as PNG or JPG files.

Specifications
Install a programming text editor if you don't already have one. I recommend Atom, but you can use NotePad++ (Windows) or TextWrangler (Macintosh). You can use any editor you like but TextEdit (Mac), NotePad(Win), and Microsoft Word are *not* acceptable as program editors.

I suggest XAMPP for Windows and MAMP for Macintosh. They all function very similar. But all the course examples will be with MAMP and the course staff will be most familiar with problem arising when you use MAMP so if you choose another environment you should be prepared to do your own tech support.

Navigate to the phpMyAdmin application select the "SQL" tab and type

SELECT 'January 3, 2017'

Replace the example date with the current date, press the "GO" button and then take a screen shot of the resulting page.

Following the instructions in the installation documents, make sure that display_errors = On in your php.ini file. Make the screen shot to show you did it as follows:

• For MAMP, go to the phpinfo page in your environment and scroll down until you can take a screen shot showing that your php.ini is correct and take a screen shot
• For XAMPP, go to Apache / Config and open the php.ini file and scroll down to show that display_errors = On and take a screen shot.
• For Linux, use any editor to open your php.ini file and take a screen shot showing display_errors = On.

Turn those two screen shots in the peer-graded assignment in the: Peer Graded: Installing PHP/MySQL link.

Sample Screen Shots
The first screen shot for the peer-graded portion of the assignment is the phpMyAdmin as follows:

Don't worry about having fewer databases on the left hand side in your screen shot since you just finished installing your server.

Here a sample of the second screen shot in the peer-graded portion is to verify that display_errors is On in your PHP configuration:

Here is a sample of a screenshot if you are using XAMPP:

WEEK 2 - Introduction to Structured Query Language (SQL)

We learn about single table queries and the basic syntax of the SQL language.

RESOURCES

"The Missing Link" Textbook Chapter: 37, 39, 41

Assignment Resources
SQL Handout (Week 2 Basic SQL Operations)

PDF Relational databaseand MySQL

• Elizabeth Fong: Creating the SQL Database Standards
• http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
• http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Assignment Specifications: Single-Table SQL

Example walkthrough for assignment

Perform the instructions below and enter the code you get into the following assignment. (Hint: starts with 493)

Specifications
First, create a MySql database or use an existing database (make sure to use a UTF8 character set) and then create a table in the database called "Ages":

CREATE TABLE Ages ( 

  name VARCHAR(128), 

  age INTEGER

)

Then make sure the table is empty by deleting any rows that you previously inserted, and insert these rows and only these rows with the following commands:

Sample Data

DELETE FROM Ages;

INSERT INTO Ages (name, age) VALUES ('Zahra', 34);

INSERT INTO Ages (name, age) VALUES ('Olufunke', 23);

INSERT INTO Ages (name, age) VALUES ('Choire', 38);

INSERT INTO Ages (name, age) VALUES ('Makenna', 32);

INSERT INTO Ages (name, age) VALUES ('Ula', 16);

INSERT INTO Ages (name, age) VALUES ('Garren', 40);

For the assignment use the data on your individual autograder page, not the sample data above.

Once the inserts are done, run the following SQL command:

SELECT sha1(CONCAT(name,age)) AS X FROM Ages ORDER BY X

Find the first row in the resulting record set and enter the long string that looks like 254c6127cdbc4c38e065317667340e8b0950046f (this is just a sample string). Use the hint as a guide.

Sample data output:

WEEK 3 - Database Design

Covering database design with multiple tables, foreign keys, and the JOIN operation.

RESOURCES WEEK 3 AND 4

"The Missing Link" Textbook Chapters: 38, 40, 42

Assignment Resources

• SQL Handout (Week 3 Building a Physical Data Schema)

PDF Relational Database Design

Assignment Specifications

In the following assignment, you will build a copy of the "Music" database covered in lecture. You will populate your database with tracks, artists, albums and genres that are different from the ones used in class. You must include three artists, five albums, and 20 tracks in your data. Choose a genre for each track. Your tables need to normalized as described in class.

Then you must construct and run some queries on your data and then take screen shots of those queries and submit the screen shots as your assignment.

Submitting Your Assignment

For this assignment you will hand in:

• Screen shot (JPG or PNG) of data in the Track table
• Screen shot (JPG or PNG) of all the data joined up sorted in ascending order by the album title
• Screen shot (JPG or PNG) of all of the genres for a particular artist. Hint - use JOIN, DISTINCT and WHERE

Grading
This is a relatively simple assignment. Don't take off points for little mistakes. If your peers seem to have done the assignment give them full credit. Feel free to make suggestions if there are small mistakes. Please keep your comments positive and useful. If you do not take grading seriously, the instructors may delete your response and you will lose points.

Sample Screen Shots

Optional Challenges
This section is entirely optional and is here in case you want to explore a bit more deeply and expand your code skillz. There is nothing to hand in for this challenge.

Come up with a query using GROUP BY to show the number of tracks an Artist has in each Genre. You do not need to hand in a screen shot of this query.

Peer Grader: Multi-Table Databases (Tracks)
In this assignment, you will build a copy of the Music database covered in lecture. You will populate your database with tracks, artists and albums that are different from the ones used in class. You must include three artists, five albums, and 20 tracks in your data. Your tables need to normalized as described in lecture.

Assignment: https://www.wa4e.com/assn/sql-tracks/

WEEK 4 - Many-To-Many

We learn how to model many-to-many relationships like those needed to represent users, roles, and courses.

Assignment Specifications: Many-to-Many Databases

Example: Walkthrough for assignment
Perform the instructions below and upload your JSON export of the resulting database in your individual assignment page.

Tables for the Assignment
Create the following tables in a database named "roster". Make sure that your database and tables are named exactly as follows including matching case.

DROP TABLE IF EXISTS Member;

DROP TABLE IF EXISTS `User`;

DROP TABLE IF EXISTS Course;

CREATE TABLE `User` (

    user_id     INTEGER NOT NULL AUTO_INCREMENT,

    name        VARCHAR(128) UNIQUE,

    PRIMARY KEY(user_id)

) ENGINE=InnoDB CHARACTER SET=utf8;

CREATE TABLE Course (

    course_id     INTEGER NOT NULL AUTO_INCREMENT,

    title         VARCHAR(128) UNIQUE,

    PRIMARY KEY(course_id)

) ENGINE=InnoDB CHARACTER SET=utf8;

CREATE TABLE Member (

    user_id       INTEGER,

    course_id     INTEGER,

    role          INTEGER,

    CONSTRAINT FOREIGN KEY (user_id) REFERENCES `User` (user_id)

      ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT FOREIGN KEY (course_id) REFERENCES Course (course_id)

       ON DELETE CASCADE ON UPDATE CASCADE,

    PRIMARY KEY (user_id, course_id)

) ENGINE=InnoDB CHARACTER SET=utf8;

Example: Data
You will normalize the following data (each user gets different data on the autograder page), and insert the following data items into your database, creating and linking all the foreign keys properly. Encode instructor with a role of 1 and a learner with a role of 0.

Taliesin, si106, Instructor

Denver, si106, Learner

Juwairiyah, si106, Learner

Kainui, si106, Learner

Zoya, si106, Learner

Aisha, si110, Instructor

Artemis, si110, Learner

Danna, si110, Learner

Dennis, si110, Learner

Tyler, si110, Learner

Kirstin, si206, Instructor

Allisha, si206, Learner

Carra, si206, Learner

Idahosa, si206, Learner

Iliana, si206, Learner

You can test to see if your data has been entered properly with the following SQL statement.

SELECT User.name, Course.title, Member.role

FROM User JOIN Member JOIN Course

ON User.user_id = Member.user_id AND Member.course_id = Course.course_id

ORDER BY Course.title, Member.role DESC, User.name

The order of the data and number of rows that comes back from this query should be the same as above. There should be no missing or extra data in your query.

Submitting Your Assignment

When you have the data all inserted, use phpMyAdmin to Export the data as follows:

• Select the database (do not select a table within the database)
• Select the Export Tab
• Select "Custom - display all possible options"
• Select "Save output to a file"
• Set the format to JSON
• Do not select "pretty print" the output
• Leave everything else as default and run the export.

The output will be on a file named "roster.json" that should look like the following: Depending on the version of phpMyAdmin there are 2 formats it exports.

/**

 Export to JSON plugin for PHPMyAdmin

 @version 0.1

 */

// Database 'roster'

// roster.Course

[{"course_id":"6","title":"si106"}, ... }]

// roster.Member

[{"user_id":"1","course_id":"1","role":"1"}, ... }]

// roster.User

[{"user_id":"15","name":"Areez"}, ... }]

[

{"type":"header","version":"4.7.0","comment":"Export to JSON plugin for PHPMyAdmin"},

{"type":"database","name":"roster"},

{"type":"table","name":"course","database":"roster","data":

[

{"course_id":"1","title":"si106"},

....

]

}

,{"type":"table","name":"member","database":"roster","data":

[

{"user_id":"1","course_id":"1","role":"1"},

....

]

}

,{"type":"table","name":"user","database":"roster","data":

[

{"user_id":"6","name":"Aisha"},

....

]

}

]

It is a somewhat strange format - it is one bit of JSON for each table. You don't need to edit or even look at this file. Simply upload it above.

 Thank's for Reading