FSD_UNIT5

1) Write About DDL, DML and DCL Commands ?
Ans:
In the context of relational databases like SQL, there are three types of SQL commands:
1. **DDL (Data Definition Language):**

- DDL commands are used to define and manage the structure of the database, including tables, indexes, and
constraints.
Examples:
- **CREATE TABLE:** This command is used to create a new table in the database. For example:
```sql
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(10, 2)
);
```
- **ALTER TABLE:** This command is used to modify an existing table structure. For example, adding a new column:
```sql
ALTER TABLE Employees
ADD Email VARCHAR(100);
```
- **DROP TABLE:** This command is used to delete an existing table and all its data. For example:
```sql
DROP TABLE Employees;
```
2. **DML (Data Manipulation Language):**

- DML commands are used to manipulate data within the database, such as inserting, updating, and deleting
records.
Examples:
- **INSERT INTO:** This command is used to add new records into a table. For example:
```sql
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (1, 'John', 'Doe', 50000);
```
- **UPDATE:** This command is used to modify existing records in a table. For example:
```sql
UPDATE Employees
SET Salary = 55000
WHERE EmployeeID = 1;
```
- **DELETE:** This command is used to remove records from a table. For example:
```sql
DELETE FROM Employees
WHERE EmployeeID = 1;
```
3. **DCL (Data Control Language):**

- DCL commands are used to control access to data within the database, including permissions and privileges.
Examples:
- **GRANT:** This command is used to give specific privileges to a user or role. For example, granting SELECT
permission on a table:
```sql
GRANT SELECT ON Employees TO User1;
```
- **REVOKE:** This command is used to take away previously granted privileges. For example, revoking the SELECT
permission:
```sql
REVOKE SELECT ON Employees FROM User1;
```
2) write a program to insert a record using spring jdbc
Ans:
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class SimpleSpringJDBCExample {
public static void main(String[] args) {

// Create a DataSource (H2 in-memory database for this example)
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.h2.Driver");
dataSource.setUrl("jdbc:h2:mem:test");
dataSource.setUsername("sa");
dataSource.setPassword("");
// Create a JdbcTemplate
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
// Create a table (for the sake of this example)

jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS employee (id INT PRIMARY KEY, name VARCHAR(255),
department VARCHAR(255))");
// Insert a record into the database

String insertSql = "INSERT INTO employee (id, name, department) VALUES (?, ?, ?)";
jdbcTemplate.update(insertSql, 1, "John Doe", "IT");
System.out.println("Record inserted successfully.");
// Clean up and close the DataSource (not needed for long-running applications)
dataSource.getConnection().close();
}
}
3) Write a program to update a record using Spring JDBC.

Ans:
public class SimpleSpringJDBCUpdateExample {



// Update the record

String updateSql = "UPDATE employee SET department = ? WHERE id = ?";
jdbcTemplate.update(updateSql, "HR", 1);
System.out.println("Record updated successfully.");
}
}
4) Write a program to delete a record using spring JDBC.

Ans:
public class SimpleSpringJDBCDeleteExample {



// Delete the record

String deleteSql = "DELETE FROM employee WHERE id = ?";
jdbcTemplate.update(deleteSql, 1);
System.out.println("Record deleted successfully.");
}
}
5) Write a simplified program to perform any DML operation using Spring JDBC.
Ans:
public class SimpleSpringJDBCDMLExample {



executeDML(jdbcTemplate, insertSql, 1, "John Doe", "IT");
// Update the record

String updateSql = "UPDATE employee SET department = ? WHERE id = ?";
executeDML(jdbcTemplate, updateSql, "HR", 1);
System.out.println("Record updated successfully.");
// Delete the record

String deleteSql = "DELETE FROM employee WHERE id = ?";
executeDML(jdbcTemplate, deleteSql, 1);
System.out.println("Record deleted successfully.");
}
private static void executeDML(JdbcTemplate jdbcTemplate, String sql, Object... args) {

jdbcTemplate.update(sql, args);
}
}
6) Write a program to read records using Spring JDBC.

Ans:
import java.util.List;
import java.util.Map;
public class SimpleSpringJDBCReadExample {


// Insert some records into the database (for demonstration purposes)

insertSampleData(jdbcTemplate);
// Read and display records from the "employee" table

List<Map<String, Object>> records = jdbcTemplate.queryForList("SELECT * FROM employee");
for (Map<String, Object> record : records) {

System.out.println("ID: " + record.get("id") + ", Name: " + record.get("name") + ", Department: " +
record.get("department"));
}
}
private static void insertSampleData(JdbcTemplate jdbcTemplate) {

jdbcTemplate.update("INSERT INTO employee (id, name, department) VALUES (?, ?, ?)", 1, "John Doe", "IT");
jdbcTemplate.update("INSERT INTO employee (id, name, department) VALUES (?, ?, ?)", 2, "Jane Smith", "HR");
}
}

FSD_UNIT5

Uploaded by

Copyright:

Available Formats

FSD_UNIT5

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

FSD_UNIT5

Uploaded by

Copyright:

Available Formats

1) Write About DDL, DML and DCL Commands ?

1. **DDL (Data Definition Language):**

2. **DML (Data Manipulation Language):**

3. **DCL (Data Control Language):**

public class SimpleSpringJDBCExample {

public static void main(String[] args) {

// Create a table (for the sake of this example)

// Insert a record into the database

System.out.println("Record inserted successfully.");

3) Write a program to update a record using Spring JDBC.

public class SimpleSpringJDBCUpdateExample {

public static void main(String[] args) {

// Create a table (for the sake of this example)

// Insert a record into the database

System.out.println("Record inserted successfully.");

// Update the record

System.out.println("Record updated successfully.");

4) Write a program to delete a record using spring JDBC.

public class SimpleSpringJDBCDeleteExample {

public static void main(String[] args) {

// Create a table (for the sake of this example)

// Insert a record into the database

System.out.println("Record inserted successfully.");

// Delete the record

public class SimpleSpringJDBCDMLExample {

public static void main(String[] args) {

// Create a table (for the sake of this example)

// Insert a record into the database

// Update the record

// Delete the record

private static void executeDML(JdbcTemplate jdbcTemplate, String sql, Object... args) {

6) Write a program to read records using Spring JDBC.

public class SimpleSpringJDBCReadExample {

public static void main(String[] args) {

// Create a table (for the sake of this example)

// Insert some records into the database (for demonstration purposes)

// Read and display records from the "employee" table

for (Map<String, Object> record : records) {

private static void insertSampleData(JdbcTemplate jdbcTemplate) {

You might also like

1. DDL (Data Definition Language):

2. DML (Data Manipulation Language):

3. DCL (Data Control Language):