NJC’s flagship internship program coding round

Table of contents

• Introduction
• MOVIES Table
• Queries
• 1st Query
• 2nd Query
• 3rd Query
• 4th Query
• 5h Query
• 6th Query
• 7th Query
• 8th Query
• 9th Query
• 10th Query

Introduction

This repository is created as a part of NJC's MuleSoft flagship Internship program. It contains a movie table with name, actor, actress, director, year of release as its attributes. Data is retrieved from the table by using queries, with the help of SQL.

1. Created "movie_db" database with table name "MOVIES" and stored details of movies.
2. File '_dbconnect.php' is used to connect to XAMPP server.
3. File 'queries.php' is used to execute queries and display the output table in website.
4. File 'movies.sql' contains all the SQL queries used in this test.

Table stored in the database

Queries

1. Retrieve all the attributes in table.

SELECT * FROM MOVIES;

2. Retrieve actor names.

SELECT DISTINCT actor FROM movies ORDER BY actor;

3. Retrieve actress names.

SELECT DISTINCT actress FROM movies ORDER BY actress;

4. Retrieve Movie name and director name of movies with actor name as Darshan thoogudeepa.

SELECT name, director FROM movies WHERE actor = 'darshan thoogudeepa';

5. Retrieve details of movies released after year 2020.

SELECT name, actor, actress, director FROM movies WHERE year_of_release > 2020;

6. Retrieve details of movie named as Free Guy.

SELECT * FROM movies WHERE name = 'FREE GUY';

7. Retrieve the number of movies released in the year 2019.

SELECT count(*) AS Movies_2019 FROM movies WHERE year_of_release = 2019;

8. Retrieve movie name, director name and year of release which is arranged with respect to director's name in ascending order.

SELECT name, director, year_of_release FROM movies ORDER BY director;

9. Retrieve details of movies released in between the year 2015 and 2020 in descending order.

SELECT name, actor, actress, year_of_release FROM movies WHERE year_of_release BETWEEN 2015 AND 2020 ORDER BY year_of_release DESC;

10. Retrieve movie name and year of release of movies which are not released in the year 2021, arranged with respect to year of release in ascending order.

SELECT name, year_of_release FROM movies WHERE year_of_release <> 2021 ORDER BY year_of_release;