Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve data from relational databases like MySQL, Oracle, etc. In this article, we will be using the Microsoft SQL Server
Here we are going to see how to get the names of the students. Here, we will first create a database named “GEEKSFORGEEKS” and then we will create a table “student” in that database. After, that we will execute our query on that table.
Steps:
Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.
Query:
CREATE DATABASE GEEKSFORGEEKS;
Output:
After executing the above command in the SQL server observing the schemas window on the left was updated with geeks for geeks. database.
database is created
Step 2: Use the GEEKSFORGEEKS database. For this use the below command.
Query:
USE GEEKSFORGEEKS
Output:
Step 3: Create a table STUDENT inside the database GEEKSFORGEEKS. This table has 2 columns namely std_id, name containing the id, and name of various students. std_id is the primary key for this table.
Query:
Create table If Not Exists Student
(std_id int, name varchar(40));
truncate table Student;
Output:
Student table:
std_id is the primary key for this table. This table contains the ID and the name of the student.
Step 4: Insert 3 rows into the Student table.
Query:
insert into Student (std_id, name)
values ('1', 'JaNU');
insert into Student (std_id, name)
values ('2', 'bOB');
insert into Student (std_id, name)
values ('3', 'bINdu');Output:
Step 5: Display all the rows of the Student table.
Query:
SELECT * FROM geeksforgeeks.student;
Output:
Step 6:
1. It takes the name from column names and checks the first character of each string by LEFT(name,1), and using UPPER() it converts 1st character to uppercase.
2. Similarly LOWER() converts other elements from the end of the string by using RIGHT(name, LENGTH(name-1)).
- LEFT(): This function extracts a number of characters from a string (starting from the left).
- RIGHT(): This function extracts a number of characters from a string (starting from the right).
- UPPER(): This function in SQL Server helps to convert all the letters of the given string to Uppercase. If the given string contains special characters or numeric values, then they will remain unchanged by this function.
- CONCAT(): This function in SQL Server helps to concatenate two or more strings together. CONCAT() function can accept a minimum of 2 parameters and a maximum of 254 parameters.
Query:
SELECT std_id, CONCAT(UPPER(LEFT(name,1)), LOWER(RIGHT(name,LENGTH(name)-1))) AS name FROM student ORDER BY std_id ASC;
Output:
Finally, we can see the names of the student in the student table with the first character being upper and the rest are lower.