From large corporations to small startups, businesses rely on data to make informed decisions, gain critical insights, and drive innovation. To effectively manage and analyze data, specialized tools and languages are required. One such language that has become a foundation of data management and analysis is SQL.

Since its inception in the 1970s, SQL has revolutionized the way businesses handle and process data. It has become the lingua franca of databases, enabling seamless communication between applications and database systems. SQL’s simplicity and versatility have made it the go-to language for managing and manipulating data, driving innovation across industries. From e-commerce platforms utilizing SQL for personalized recommendations to healthcare organizations leveraging it for analyzing patient data, SQL has transformed how we interact with information and become a key element of modern technology.

In this blog post, we’ll explore the world of SQL and its significance in the tech industry. Whether you are a hiring manager looking to understand the value of SQL skills or a tech professional interested in expanding your knowledge, this comprehensive guide will provide valuable insights into the power and versatility of this relational database language. 

What is SQL?

SQL, short for Structured Query Language, is a programming language designed for managing and manipulating relational databases. It serves as a standard interface for interacting with databases and performing operations such as data retrieval, insertion, modification, and deletion. SQL provides a structured approach to organizing and accessing data, making it an essential tool for data engineers, data scientists, data analysts, and software developers.

At its core, SQL operates on the concept of a relational database, which consists of tables that store data in rows and columns. These tables are interconnected through relationships, allowing for efficient and organized data storage. SQL provides a rich set of commands, known as queries, to interact with these databases and perform various tasks.

Let’s explore some fundamental aspects of SQL.

Data Definition Language (DDL)

SQL includes a set of commands for defining and modifying the structure of a database. With DDL statements such as CREATE, ALTER, and DROP, you can create new tables, modify existing ones, and remove unnecessary tables. DDL statements enable you to define the data types, constraints, and relationships within the database schema.

Data Manipulation Language (DML)

DML statements in SQL allow you to manipulate the data stored in the database. Commands such as SELECT, INSERT, UPDATE, and DELETE enable you to retrieve specific data, insert new records, update existing records, and delete unwanted data. DML provides the flexibility to perform complex operations on the database tables.

Querying and Retrieving Data

One of the primary strengths of SQL is its ability to query and retrieve data from databases. The SELECT statement is used to specify the columns to retrieve and the conditions to filter the data. SQL provides various clauses like WHERE, ORDER BY, and GROUP BY to refine the query results and sort the data based on specific criteria. This querying capability allows for efficient data retrieval and analysis.

Data Integrity and Constraints

SQL supports data integrity through constraints. Constraints ensure the accuracy and consistency of data stored in the database. SQL provides different types of constraints, such as primary key, foreign key, unique, and check constraints, to enforce rules and relationships within the data. These constraints help maintain data integrity and prevent inconsistencies.

Joins and Relationships

SQL allows you to establish relationships between tables using joins. Joins combine data from multiple tables based on related columns, enabling you to retrieve data that spans across tables. SQL supports different types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, providing flexibility in querying related data.

SQL’s versatility extends beyond relational databases. It also offers extensions and features for handling large datasets, working with non-relational databases, and performing advanced analytics. These extensions, such as window functions, common table expressions (CTEs), and aggregate functions, enhance SQL’s capabilities and make it suitable for complex data analysis tasks.

Advantages of SQL

SQL has long been considered the industry-standard language in relational database communication. With a battle-tested track record and a robust ecosystem of resources, SQL remains a popular choice for database projects. Here, we’ll dive into some of the key advantages that have contributed to SQL’s widespread adoption.

Standardization

SQL is a standardized language that follows a set of rules and syntax defined by the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI). This standardization ensures that SQL is consistent across different database management systems (DBMS). Developers and data professionals can leverage their SQL skills across various platforms without the need to learn different languages or techniques for each specific DBMS.

Ease of Use 

SQL offers a user-friendly and intuitive syntax that makes it relatively easy to learn and use. Its declarative nature allows users to focus on specifying what data they want to retrieve or manipulate rather than worrying about how to achieve it. The SQL queries resemble natural language, making them more accessible to individuals with minimal programming experience.

Data Integrity and Security

SQL provides robust mechanisms for maintaining data integrity and enforcing security measures. Through constraints, SQL ensures that data stored in databases adheres to predefined rules, preventing data inconsistencies. Additionally, SQL offers features such as user authentication, role-based access control, and encryption, which enhance the security of sensitive data.

Flexibility and Extensibility

SQL’s flexibility allows users to perform a wide range of operations on data. It supports complex queries, aggregations, sorting, and filtering, enabling users to extract valuable insights from datasets. Moreover, SQL has evolved beyond its traditional relational roots and now offers extensions for handling non-relational data, performing advanced analytics, and integrating with other programming languages.

Industry Support and Community

SQL has a vast and active community of developers, data professionals, and database vendors who contribute to its growth and development. This community-driven ecosystem provides access to a wealth of resources, including online forums, tutorials, documentation, and libraries, making it easier for users to find help, share knowledge, and stay up to date with the latest SQL advancements.

Integration with Other Tools and Technologies

SQL seamlessly integrates with a wide range of tools and technologies commonly used in the data ecosystem. It can be integrated with programming languages like Python, Java, or C#, enabling developers to incorporate SQL queries into their applications. SQL also integrates with popular data analysis and visualization tools, making it easier to extract insights and present data in a meaningful way.

Industries Using SQL

SQL’s versatility and power make it an indispensable tool for various data-related tasks. Let’s delve into some practical use cases where SQL shines and demonstrates its effectiveness in solving real-world data challenges.

• E-commerce and Retail: SQL is used extensively for managing product catalogs, tracking customer behavior, and analyzing sales data. SQL queries can help identify popular products, calculate revenue by category or region, monitor inventory levels, and generate personalized recommendations based on customer preferences. 
• Financial Services: SQL plays a critical role in the financial services sector for tasks such as risk analysis, fraud detection, and regulatory compliance. Financial institutions utilize SQL to query and analyze vast amounts of transactional data, identify patterns of suspicious activity, and generate reports for auditors and regulators. 
• Healthcare and Medical Research: SQL is employed in healthcare organizations and medical research facilities to manage patient records, track medical procedures, and analyze clinical data. SQL queries can help identify disease patterns, track treatment outcomes, and conduct population health studies. 
• Marketing and Advertising: Marketers use SQL to analyze campaign performance, customer segmentation, and advertising effectiveness. Marketers can use SQL to query customer databases and extract valuable insights for targeted marketing campaigns. SQL is also used for analyzing web analytics data, tracking website conversions, and measuring the success of online advertising campaigns. 
• Data Analysis and Business Intelligence: SQL is a fundamental tool for data analysts and business intelligence professionals across industries. These roles involve querying and manipulating data, generating reports and dashboards, and conducting data-driven analyses. 
• Human Resources: SQL is utilized in human resources for managing employee data, generating reports, and conducting workforce analytics. SQL queries can help HR professionals track employee performance, analyze training and development programs, and generate reports on employee demographics and diversity. 
• Logistics and Supply Chain: SQL is applied in logistics and supply chain management to track inventory levels, manage warehouse operations, and optimize logistics networks. SQL queries can help monitor stock levels, identify demand patterns, and streamline supply chain processes. 

SQL Hiring Trends

The increasing reliance on data-driven decision-making has fueled the demand for professionals who can effectively manage and analyze data. SQL, being a powerful language for data manipulation and retrieval, has become one of the most sought-after skills in tech. In our latest Developer Skills Report, we found that demand for SQL skills grew in 2022, putting it in third place on our list of the most in-demand programming languages — and even surpassing C++. 

This growth in demand is largely driven by the fact that SQL proficiency is a fundamental requirement for many data-related roles. Data analysts, data scientists, database administrators, and business intelligence specialists all use SQL to perform various data-related tasks. As the need for all types of data professionals has grown — fueled by advancements in artificial intelligence, machine learning, and Big Data— so too has demand for professionals who can leverage SQL effectively. 

Proficiency in SQL not only expands career opportunities but also positions individuals for career growth. It serves as a solid foundation for learning other data-related technologies and languages, allowing professionals to adapt to evolving industry trends and stay ahead in the competitive job market.

To learn more about the types of roles that require SQL skills and stay up to date on the latest trends, check out our roles directory.

This article was written with the help of AI. Can you tell which parts?

The post What Is SQL? A Guide to the Relational Database Language appeared first on HackerRank Blog.

Are you thinking about earning an SQL certification to improve your career prospects? If so, you’re not alone. SQL (Structured Query Language) has become one of the most popular and in-demand programming languages in the world.

An SQL certification can be a great way to develop your database skills and advance your career. But there are many different SQL certificates that you can earn, and it can be overwhelming to try to figure out which one is the best right for you. In this blog post, we’ll take a look at some of the top SQL certificates that you can earn.

The Importance of SQL Certifications

SQL (structured query language) is the industry-standard language for working with relational databases. Used for creating, defining, and maintaining databases, SQL is a vital skill for data scientists and software engineers.

Now is a great time to further your SQL skills with a certification. In 2022, SQL was the third most in-demand language and grew faster than the average for all languages.

Earning an SQL certification has a number of career benefits, including:

• Expanding your knowledge base
• Improving your potential earning power
• Earning a digital badge you can add to LinkedIn
• Validating your skills to appeal to potential employers
• Learning to perform complex, hands-on database activities
• Gaining exposure to the tasks you’ll use on the job

There are many different SQL certificates that you can earn, and the best one for you will depend on your specific goals and interests. Whichever certification you choose, it can be a valuable addition to your resume and can help you to advance your career.

The right certification will also depend on the technologies you intend to work with. For example, a developer who primarily works with Oracle databases should consider Oracle’s certification program.

In general, there are two types of SQL certifications: 

• Courses by education providers that teach foundational skills
• Vendor-specific certifications that teach advanced skills for a particular technology

So, is pursuing an SQL certification worthwhile? 

Any certification will require a time investment, and some have an upfront cost. Whether an SQL certification is worth this investment will depend on the individual and the specific certification. With so many opportunities for professionals who know SQL, improving your database skills will always go a long way in building a successful career and increasing your earning potential. But the value of having a credential to use during your job search will depend on your career goals. 

Top SQL Certifications

While selecting a certification, pay careful attention to whether the program is active. Large technology companies often withdraw or retire their certifications programs, and may or not replace them with a new certification. Any list of SQL certifications runs the risk of sharing a link to a withdrawn course. You should also keep in mind that some certifications expire. If maintaining an SQL certification is important for your career, you may need to redo the program every few years.

Oracle Database SQL Certified Associate

• Duration: 2 hours
• Cost: $245
• Experience level: Beginner

The Oracle Certified Associate certification is for professionals who have experience working with the Oracle Database and want to prove their skill with the technology. To earn the Oracle certification, you’ll need to demonstrate competency in topics such as data modeling, database administration, and SQL.

The certification also covers hands-on database actions, such as

• Inserts
• Queries
• Optimizers
• Statements
• Data control
• Data definition
• Normalization
• Indexes
• Tables

MySQL Certification Paths

• Duration: 1.5–2 hours
• Cost: $245
• Experience level: Professional or Specialist

The MySQL Certification Paths are a valuable series of certificates for professionals who work with MySQL, another database management system. This entry on our list is actually a broad category of certifications offered by Oracle for MySQL. (Oracle owns MySQL, so don’t be surprised if you see both of these names in a course title.)

The certifications under the MySQL Certification Paths include:

• Oracle Certified Professional, MySQL 5.7 Database Administrator
• Oracle Certified Professional, MySQL 8.0 Database Administrator
• MySQL 2021 Certified Implementation Specialist
• MySQL 8.0 Database Developer Oracle Certified Professional

The various MySQL certifications cover topics such as SQL, data modeling, database administration, connectors, APIs, schema objects, and transactions.

HackerRank SQL Certifications

HackerRank offers an SQL certification that will test your knowledge of SQL and relational databases. Part of HackerRank’s series of skills certification tests, these assessments will evaluate your technical proficiency.  

The certification exams cover topics such as basic SQL commands, data types and constraints, and advanced queries. Once you pass the certification exam, you will receive an SQL certification from HackerRank, which can be added to your resume to demonstrate your proficiency in SQL.

HackerRank offers certifications for three experience levels:

• Basic SQL Skills
  • Duration: 30 minutes
  • Concepts: simple queries, relationships, and aggregators
• Intermediate SQL Skills
  • Duration: 35 minutes
  • Concepts: complex joins, unions, and sub-queries.
• Advanced SQL Skills
  • Duration: 60 minutes
  • Concepts: query optimization, data modeling, Indexing, window functions, and pivots.

Microsoft Azure SQL Course

• Experience Level: Beginner
• Duration: 7 hours 
• Course Cost: Free
• Certification Cost: $99

The Microsoft Azure Data Fundamentals Certification is a five part series that includes the Microsoft Azure SQL course. This course is ideal for IT professionals looking to learn the fundamentals of database concepts in a cloud environment and build foundational knowledge of cloud data services within Microsoft Azure. 

The course covers a range of database topics, including:

• Queries 
• SQL dialects
• Provisioning 
• Deploying
• Querying 
• Database maintenance
• Relational data offerings
• Cloud data solutions

PostgreSQL 12 Associate Certification by EDB

• Cost: $200
• Duration: 60 minutes
• Experience level: Intermediate

The PostgreSQL 12 Associate Certification by EnterpriseDB (EDB) is a certification exam that assesses the ability of a database professional to maintain and manage a PostgreSQL server in a production environment. Professionals who pass receive a personalized PDF certification and badge from EDB.

The PostgreSQL certification covers topics such as:

• Foundational SQL knowledge
• Familiarity with Postgres
• Database installation
• User management

IBM Certified Database Administrator

• Duration 90 minutes
• Experience level: Intermediate

IBM Certified Database Administrator certification is a certification exam that includes two exams and an optional preparation course. We’ve included the IBM certification because it is a popular certification with a key database technology. However, IBM regularly withdraws their certifications, which means that the program is inactive until they create a new one. For this reason, there’s a chance the IBM certification is inactive while you’re conducting your research. The link is for the most recent certification, which was withdrawn before publication. Be sure to verify the status of the IBM certification (or any certification, for that matter) before you proceed.

Topics covered in this certification typically include:

• Physical design
• Database security
• Server management
• Business rules implementation

Resources for Learning SQL

HackerRank SQL Practice Problems

7 Advanced SQL Interview Questions

The post The 6 Best SQL Certifications in 2023 appeared first on HackerRank Blog.

Database interview questions have been a critical component of technical hiring for decades. If you’re a data scientist, data engineer, or software engineer on the job market, the ability to demonstrate your skills in a database interview is critical to landing your next role. 

To succeed in a database interview, you’ll need to stay up to date on the latest advancements and prepare for the styles of problems you might encounter. In this post, we’ll review the conceptual, basic, and advanced database interview questions you need to know to land your dream job.

What a Database Interview Looks Like

A database is an “organized collection of structured information,” typically stored in a database management system (DBMS).

During a database interview, candidates are challenged to complete a series of queries or functions using accompanying sets of data tables, input formats, and output formats.

Database interview questions can cover a wide range of database concepts, including: 

• Queries
• Normalization
• De-normalization
• Transactions
• Subqueries
• Joining
• Ordering
• OLAP operations
• XML queries
• MapReduce

Depending on the format, the interview may also include questions about database technologies, including:

• Database management systems
• Relational databases
• Non-relational databases
• SQL
• NoSQL
• Ruby
• Python

Multiple Choice Database Questions

Multiple choice questions will test your basic knowledge of database concepts and functions. It’s worth noting that during an assessment or interview, you may encounter similar questions in an open-ended format, with no answers to choose from.

Relational Algebra

Solve Problem

In precedence of set operators the expression is evaluated from:

• Depends on the expression
• Left to right
• Right to left
• From user specification

Database Query Languages

Solve Problem

Using which language can a user request information from a database ?

• Query
• Relational
• Structural
• Compiler

Procedural Language

Solve Problem

Which one of the following is a procedural language ?

• Domain relational calculus
• Tuple relational calculus
• Relational algebra
• Query language

Index Architecture Types

Solve Problem

How many index architecture type classifications are there in MS SQL Server?

• 1
• 2
• 3
• 4

OLAP Operations

Solve Problem

This OLAP operation involves computing all of the data relationships for one or more dimensions.

• Dice
• Slice
• Pivot
• Roll-up

Basic Database Coding Questions

Below are five examples of basic problems a data scientist or software engineer might face during a technical interview. These questions are simple in nature, testing only one database concept. They are meant to be solved in a collaborative integrated development environment (IDE).

Basics of Sets and Relations #1

Solve Problem

You are given two sets.

Set A = {1,2,3,4,5,6}

Set B = {2,3,4,5,6,7,8}

How many elements are present in A U B?

Only enter the correct integer in the editor below. Do not include any extra spaces, tabs or newlines.

Basics of Sets and Relations #3

Solve Problem

You are given two sets.

Set A = {1,2,3,4,5,6}

Set B = {2,3,4,5,6,7,8}

How many elements are present in A – B?

Only enter the correct integer in the answering box. Do not include any extra spaces, tabs or newlines.

Database Normalization #1 – 1NF

Solve Problem

The following unnormalized table named PRODUCT is transformed to first normal form (1NF) by splitting it into two tables which have X and Y rows (such that X < Y) respectively. Both the tables have Z columns.

*Product-ID*     *Colors*                   *Price*
1                             Red,Green              15.0
2                            Blue                         18.0
3                            Yellow,Pink             2.5

What are the values of X, Y, Z? Enter these integers, each on a new line, in the text-box below. Do not leave any leading or trailing spaces.

Database Normalization #4

Solve Problem

A database, normalized as per 2NF rules, has been split into 10 tables. Each of the tables has exactly two columns: one key attribute and one non-key attribute. What is the minimum number of tables required to express this database in 3NF form? Enter the integer in the text box below. Do not leave any leading or trailing spaces.

Querying XML Datastores with XPath #1

Solve Problem

XPath is a valuable tool often used for querying XML databases. XPath queries (or close variants) are also used in the process of Web Scraping while retrieving data from structured XHTML-compliant web pages, specially those with tabulated data.

(A quick XPath tutorial is available here.)

Assume that you have been provided a fragment of XML, which you can view here. Your task is to write the XPath selector for listing the titles of the movies in the same order as which they occur in the given XML. The Ruby code for handling input, output, and document construction has already been provided. You only need to fill up the required blanks as indicated in the template code provided. This challenge is agnostic to language-specific knowledge and you do not require to know Ruby – as you only need to complete the XPath selector.

Advanced Database Coding Questions

Below are five examples of more challenging database problems, with difficulties ranging from medium to advanced. These questions cover more specific concepts and language-specific knowledge, and are also meant to be solved in a collaborative IDE.

MapReduce Advanced – Relational Join

Solve Problem

A SQL join combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each.

The input is a number of lines with records from two tables Employee and Department. A tuple from the Employee table will look like:

Employee [Person_Name] [SSN]

A tuple from the Department table will look like:

Department [SSN] [Department_Name]

The required output is to print the JOIN of the two tables Employee and Department, in the format shown. The code for the MapReduce class, parts related to IO etc. has already been provided. However, the mapper and reducer functions are incomplete. Your task is to fill up the mapper and reducer functions appropriately, such that the program works, and outputs the JOIN of the two tables, in lexicographical order.

Database Normalization #3

Solve Problem

A database used by a college’s application stores the relationship between students and the courses they are enrolled in. We have information for each STUDENT (such as name, date of birth, date of enrollment, student-id) and COURSE (course code, instructor, etc.). In real life, a student takes several courses simultaneously while a subject is studied by several students. We need to capture this many-to-many relationship in our database. From the above information, what is the minimum number of tables required to structure this database in accordance with the rules of 2NF normalization?

Database Normalization #9

Solve Problem

Consider the following relation and determinants. The key(s) are bolded.

R(a, b ,c,d,e)

Which of these determinants is a NON-CANDIDATE key? In the text box, only enter the index number (1-3) of the dependency which you have identified as non-key.

1. a,c -> b,d,e
2. a,d -> b
3. a,c,e -> b,d

Database Keys

Solve Problem

A database table with three fields (bookname, author, language) has been created. If the table is as provided below, which of these three fields may be used as the primary key?

bookname-author-language

A Tale of Two Cities, Charles Dickens, English

Oliver Twist, Charles Dickens, English

Godaan, Premchand, Hindi

Chandrakanta, Devaki Nandan Khatri, Hindi

Hamlet, William Shakespeare, English

The Merchant of Venice, William Shakespeare, English

Only fill in the name of the field which may be used as the primary key. Grading is case-sensitive.

Database Differences

Solve Problem

Relation R(A,B,C) has the following tuples:

A B C

1 2 3

4 2 3

4 5 6

2 5 3

1 2 6

Relation S(A,B,C) has the following tuples:

A B C

2 5 3

2 5 4

4 5 6

1 2 3

The difference (R-S) is computed and the following tuple is found to be present in the result. Assume that the schema of the result is (A,B,C).

4, b, c

Find the integers b and c. Fill in the values in the answer box, each on a new line.

Resources to Improve Database Knowledge

HackerRank Database Questions

HackerRank SQL Questions

HackerRank SQL Certification (Basic)

HackerRank SQL Certification (Intermediate)

HackerRank SQL Certification (Advanced)

HackerRank Interview

The post 15 Database Interview Questions You Should Know appeared first on HackerRank Blog.

SQL interview questions have been a critical component of technical hiring for decades. If you’re a data scientist or software engineer on the job market, the ability to demonstrate your database skills in an SQL interview is critical to landing your next role. 

Despite being over four decades old, SQL is still evolving at a rapid pace. To succeed in an SQL challenge, you’ll need to stay up to date on the latest advancements and prepare for the styles of problems you might encounter. Instead of reviewing basic database definitions and concepts, this article will challenge you with seven advanced SQL interview question examples you need to be familiar with to land your next dream job.

Overview of SQL Interview Questions

SQL (structured query language) is the industry-standard language for working with relational databases. Used for creating, defining, and maintaining databases, SQL is a vital skill for data scientists and software engineers.

During an SQL interview problem, candidates receive sets of data tables, input formats, and output formats and are challenged to perform a series of queries or functions with that data. 

SQL interview questions can cover a wide range of database concepts, including normalization, transactions, subqueries, joining, and ordering.

While some interview questions cover basic-level questions – what is normalization? – experienced engineers and data scientists will encounter problems that test their SQL skills through hands-on coding.

Depending on the employer’s technical interviewing tool, candidates can choose from a range of relational database tools, including DB2, MySQL, Oracle, and MS SQL Server.

7 Advanced SQL Interview Questions

Below are seven examples of the kinds of problems a data scientist or software engineer might face during a technical interview. These questions all test SQL and relational database skills, and are meant to be solved in a collaborative integrated development environment (IDE).

To view the the data tables that accompany each question, click the solve problem link.

Print Prime Numbers

Solve Problem

Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).

For example, the output for all prime numbers <= 10 would be:

2&3&5&7

New Companies

Solve Problem

Amber’s conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy: Founder → Lead Manager → Senior Manager → Manager → Employee

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

The tables may contain duplicate records. The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

Weather Observation Station

Solve Problem

Consider P1(a,b) and P2(c,d)  to be two points on a 2D plane.

• a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
• b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
• c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
• d happens to equal the maximum value in Western Longitude (LONG_W in STATION).

Query the Manhattan Distance between points P1 and P2 and round it to a scale of 4 decimal places.

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

Binary Tree Nodes

Solve Problem

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

• Root: If node is root node.
• Leaf: If node is leaf node.
• Inner: If node is neither root nor leaf node.

Question: Tenured Employees

Concepts Covered: SQL (Basic), JOIN, ORDER BY

There are two data tables with employee information: EMPLOYEE and EMPLOYEE_UIN. Query the tables to generate a list of all employees who have been employed fewer than three years in order of NAME, then of ID, both ascending. The result should include the UIN followed by the NAME. While the secondary sort is by ID, the result includes UIN but not ID.

Interview Guidelines

Join the tables to get UIN. Filter results to TIME < 3 and sort ascending by name, id.

Schema

Sample Input

Challenge Question: 15 Days of Learning SQL

Solve Problem

Difficult Level: Hard

Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.

Write a query to print the total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day (without considering if they made submissions the days before or after). If more than one such hacker has the maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

Input Format

The following tables hold contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
• Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission.

Challenge Question: Interviews

Solve Problem

Difficulty Level: Hard

Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are 0.

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds 1 screening contest.

Input Format

The tables hold interview data:

• Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.

• Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.

• Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.

• View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.

• Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.

Resources for SQL Interviews

HackerRank SQL Questions

HackerRank SQL Certification (Advanced)

HackerRank Interview

The post 7 Advanced SQL Interview Questions For 2022 appeared first on HackerRank Blog.