WeMo Connect

Setup Menus in Admin Panel

First Database SQL

SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;


— Database: `connect`

— ——————————————————–


— Table structure for table `calander`

CREATE TABLE `calander` (
`id` int(11) DEFAULT NULL ,
`start_time` datetime DEFAULT NULL,
`end_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

— ——————————————————–


— Table structure for table `coaches`

CREATE TABLE `coaches` (
`id` int(11) DEFAULT NULL ,
`name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

— ——————————————————–


— Table structure for table `courses`

CREATE TABLE `courses` (
`id` int(11) DEFAULT NULL ,
`name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

— ——————————————————–


— Table structure for table `location`

CREATE TABLE `location` (
`id` int(11) DEFAULT NULL ,
`name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

— ——————————————————–


— Table structure for table `projects`

CREATE TABLE `projects` (
`id` int(11) DEFAULT NULL ,
`name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

— ——————————————————–


— Table structure for table `students`

CREATE TABLE `students` (
`id` int(11) DEFAULT NULL ,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`phone` varchar(100) NOT NULL,
`skype` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `students` (`id`, `name`, `email`, `phone`, `skype`) VALUES
(1, ‘Ayesha’, ‘ayesha@hotmail.com’, ‘123-123-12345’, ‘ayesha1234’),
(2, ‘Ali’, ‘ali@gmail.com’, ‘123-123-1111’, ‘ali1111’);
— SAMPLE QUERIES ALL COURSES BY STUDENTS
— TRY1
select *
FROM students s, courses c, student_courses sc
WHERE
s.id = sc.student_id
AND c.id = sc.course_id
— TRY2
select s.name, c.name
FROM students s, courses c, student_courses sc
WHERE
s.id = sc.student_id
AND c.id = sc.course_id
— TRY3
select s.name, c.name
FROM students s, courses c, student_courses sc
WHERE
s.id = sc.student_id
AND c.id = sc.course_id
ORDER BY s.id

— TRY4
select s.name, c.name
FROM students s, courses c, student_courses sc
WHERE
s.id = sc.student_id
AND c.id = sc.course_id
ORDER BY s.name

— WHAT IS THE TOTAL PERCENTAGE OF AYESHA IN JAVA COURSES
— TRY1
SELECT *
FROM students s, grades g, courses c
WHERE
s.name = ‘Ayesha’
AND c.name = ‘Java’
AND s.id = g.student_id
AND c.id = g.course_id

— TRYE2
SELECT s.name, c.name, g.*
FROM students s, grades g, courses c
WHERE
s.name = ‘Ayesha Khan’
AND c.name = ‘Java’
AND s.id = g.student_id
AND c.id = g.course_id

— TRYE3
SELECT s.name, c.name, g.activity, g.score, g.score_max
FROM students s, grades g, courses c
WHERE
s.name = ‘Ayesha’
AND c.name = ‘Java’
AND s.id = g.student_id
AND c.id = g.course_id

— TRY4
SELECT s.name, c.name, g.activity, g.score, g.score_max, (g.score/g.score_max) * 100 AS percent
FROM students s, grades g, courses c
WHERE
s.name = ‘Ayesha’
AND c.name = ‘Java’
AND s.id = g.student_id
AND c.id = g.course_id

February 1, 2015

0 Responses on First Database SQL"

    Leave a Message

    Your email address will not be published. Required fields are marked *