Introduction to Structured Query Language (SQL) Query Solution || Coursera 2022 || Week

 You just need to run this query in phpMyAdmin and according to given input data you just write in the user table. And after run the below query you click on export menu in the up side and follow the instructions. And First of all create database named as "roster"

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.

NOTE : "DON'T CHANGE THE USER_ID " you only change the name.

Rest of the query is same.

So here is the query 

CREATE TABLE `course` (

  `course_id` int(11) NOT NULL,

  `title` varchar(128) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `course` (`course_id`, `title`) VALUES

(1, 'si106'),

(2, 'si110'),

(3, 'si206');


CREATE TABLE `member` (

  `user_id` int(11) NOT NULL,

  `course_id` int(11) NOT NULL,

  `role` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `member` (`user_id`, `course_id`, `role`) VALUES

(1, 1, 1),

(2, 1, 0),

(3, 1, 0),

(4, 1, 0),

(5, 1, 0),

(6, 2, 1),

(7, 2, 0),

(8, 2, 0),

(9, 2, 0),

(10, 2, 0),

(11, 3, 1),

(12, 3, 0),

(13, 3, 0),

(14, 3, 0),

(15, 3, 0);


CREATE TABLE `user` (

  `user_id` int(11) NOT NULL,

  `name` varchar(128) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `user` (`user_id`, `name`) VALUES

(2, 'Kirsten'),

(3, 'Malebo'),

(12, 'Braydon'),

(13, 'Cassy'),

(4, 'Nate'),

(11, 'Yingzi'),

(7, 'Ellisha'),

(8, 'Kyrah'),

(9, 'Peregrine'),

(10, 'Susannah'),

(14, 'Lorraine'),

(6, 'Manwen'),

(5, 'Olympia'),

(15, 'Mea'),

(1, 'Kashish');


ALTER TABLE `course`

  ADD PRIMARY KEY (`course_id`),

  ADD UNIQUE KEY `title` (`title`);


ALTER TABLE `member`

  ADD PRIMARY KEY (`user_id`,`course_id`),

  ADD KEY `course_id` (`course_id`);


ALTER TABLE `user`

  ADD PRIMARY KEY (`user_id`),

  ADD UNIQUE KEY `name` (`name`);


ALTER TABLE `course`

  MODIFY `course_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;


ALTER TABLE `user`

  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;


ALTER TABLE `member`

  ADD CONSTRAINT `member_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `member_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE;

COMMIT;


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

Comments

Post a Comment

Popular posts from this blog

Java Script Hacker Rank 2021 Certification (Chamion League Teams) Solution

Put this in your code as : const https = require('https'); const fetch = (url) => {   return new Promise((resolve, reject) => {     https       .get(url, (resp) => {         let data = '';         // A chunk of data has been recieved.         resp.on('data', (chunk) => {           data += chunk;         });         // The whole response has been received. Print out the result.         resp.on('end', () => {           resolve(JSON.parse(data));         });       })       .on('error', (err) => {         reject(err.message);       });   }); }; const getAPIURL = (year, page) => {   return `https://jsonmock.hackerrank.com/api/football_matches?competition=UEFA%20Champions%20Lea...
Read more

HackerRank Day 6: Javascript Dates 10 Days of javascript problem solution.

HackerRank Day 6: Javascript Dates 10 Days of javascript problem solution. 'use strict'; process.stdin.resume(); process.stdin.setEncoding('utf-8'); let inputString = ''; let currentLine = 0; process.stdin.on('data', inputStdin => { inputString += inputStdin; }); process.stdin.on('end', _ => { inputString = inputString.trim().split('\n').map(string => { return string.trim(); }); main(); }); function readLine() { return inputString[currentLine++]; } // The days of the week are: "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" function getDayName(dateString) { const date = new Date(dateString); const options = { weekday: 'long' }; return new Intl.DateTimeFormat('en-Us', options).format(date); } function main() { const d = +(readLine()); ...
Read more